SQL - Advanced Window Functions
Which of the following is the correct syntax to calculate a 3-row moving average of the column
sales ordered by date?Which of the following is the correct syntax to calculate a 3-row moving average of the column sales ordered by date?
easy📝 Syntax Q12 of 15
Step-by-Step Solution
Solution:
Step 1: Identify correct window function syntax
The correct syntax requiresOVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)to define ordering and frame.Step 2: Check each option
SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses ORDER BY with correct frame. SELECT date, AVG(sales) OVER (PARTITION BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; wrongly uses PARTITION BY instead of ORDER BY. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS moving_avg FROM sales_data; uses FOLLOWING which looks ahead, not behind. SELECT date, AVG(sales) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; misses ORDER BY clause.Final Answer:
SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; -> Option DQuick Check:
ORDER BY + ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = D [OK]
Quick Trick: Use ORDER BY with ROWS BETWEEN for moving averages [OK]
Common Mistakes:
- Using PARTITION BY instead of ORDER BY for moving average
- Omitting ORDER BY clause
- Using FOLLOWING instead of PRECEDING
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes