[Solved] You want to calculate a 5-day moving average of daily sales, but only include days where sales were above 100. Which SQL query correctly applies this condition inside the moving average calculation? | SQL

SQL - Advanced Window Functions

You want to calculate a 5-day moving average of daily sales, but only include days where sales were above 100. Which SQL query correctly applies this condition inside the moving average calculation?

ASELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

BSELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) WHERE sales > 100 FROM sales_data;

CSELECT date, sales, AVG(sales) FILTER (WHERE sales > 100) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

DSELECT date, sales, AVG(sales) OVER (PARTITION BY sales > 100 ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

Step-by-Step Solution

Solution:

Step 1: Understand filtering inside window functions
To include only sales > 100 in the average, use a CASE expression inside AVG to nullify unwanted rows.
Step 2: Evaluate each option
SELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses CASE to include sales > 100, replacing others with NULL (ignored by AVG). SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) WHERE sales > 100 FROM sales_data; filters rows after window function (invalid syntax). SELECT date, sales, AVG(sales) FILTER (WHERE sales > 100) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; uses FILTER clause which is not standard in all SQL versions. SELECT date, sales, AVG(sales) OVER (PARTITION BY sales > 100 ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; partitions by boolean condition, which changes grouping incorrectly.
Final Answer:
SELECT date, sales, AVG(CASE WHEN sales > 100 THEN sales ELSE NULL END) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; -> Option A
Quick Check:
Use CASE inside AVG to filter rows in window [OK]

Quick Trick: Use CASE inside AVG to filter values in window frame [OK]

Common Mistakes:

Filtering rows outside window function
Using PARTITION BY for filtering condition
Assuming FILTER clause always works

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You want to calculate a 5-day moving average of daily sales, but only include days where sales were above 100. Which SQL query correctly applies this condition inside the moving average calculation?

Step 1: Understand filtering inside window functions

Step 2: Evaluate each option

Final Answer:

Quick Check:

Want More Practice?