SQL - Advanced Window Functions
You want to calculate a 5-day moving average of sales but only for each product category separately. Which window function clause correctly achieves this?
You want to calculate a 5-day moving average of sales but only for each product category separately. Which window function clause correctly achieves this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand partitioning by category
To calculate moving average per category, usePARTITION BY category.Step 2: Define order and frame
Order by sale_date and frame as 4 preceding rows plus current row for 5-day window.Step 3: Match with options
AVG(sales) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) correctly uses PARTITION BY category, ORDER BY sale_date, and ROWS BETWEEN 4 PRECEDING AND CURRENT ROW.Final Answer:
AVG(sales) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) -> Option DQuick Check:
Partition by category + 5-day frame = AVG(sales) OVER (PARTITION BY category ORDER BY sale_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) [OK]
Quick Trick: Use PARTITION BY to separate groups in window functions [OK]
Common Mistakes:
- Omitting PARTITION BY for per-group calculation
- Swapping ORDER BY columns
- Incorrect frame boundaries
Master "Advanced Window Functions" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes