SQL - Advanced Window Functions
Which SQL query correctly computes a moving average of
revenue over the last 3 rows ordered by transaction_date?Which SQL query correctly computes a moving average of revenue over the last 3 rows ordered by transaction_date?
easy📝 Syntax Q3 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the window frame
The clauseROWS BETWEEN 2 PRECEDING AND CURRENT ROWincludes the current row and the two previous rows.Step 2: Check the ORDER BY clause
Ordering bytransaction_dateensures the rows are processed in date order.Step 3: Verify syntax correctness
SELECT transaction_date, revenue, AVG(revenue) OVER (ORDER BY transaction_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM transactions; correctly usesROWS BETWEEN 2 PRECEDING AND CURRENT ROWwithORDER BY transaction_date.Final Answer:
Option A -> Option AQuick Check:
Window frame must be between preceding and current row [OK]
Quick Trick: Use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for last 3 rows [OK]
Common Mistakes:
- Using FOLLOWING instead of PRECEDING in window frame
- Omitting ORDER BY inside OVER() clause
- Using PARTITION BY incorrectly for this calculation
- Confusing RANGE with ROWS frame specification
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes