Moving averages with window frames in SQL - Step-by-Step Execution

Concept Flow - Moving averages with window frames

Start with data rows

↓

Define window frame: rows before and after

↓

Calculate average over window frame

↓

Assign average to current row

↓

Move to next row

↓

Repeat until all rows processed

↓

Output rows with moving averages

The process takes each row, looks at a defined range of rows around it (window frame), calculates the average of values in that range, and assigns it as the moving average for that row.

Execution Sample

SQL

SELECT day, sales,
       AVG(sales) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM sales_data
ORDER BY day;

Calculates a moving average of sales for each day using the current day, one day before, and one day after.

Execution Table

Step	Current Row (day)	Window Frame Rows	Values in Frame	Average Calculated	Output moving_avg
1	1	[1,2]	[100, 120]	110	110.0
2	2	[1,2,3]	[100, 120, 130]	116.67	116.67
3	3	[2,3,4]	[120, 130, 90]	113.33	113.33
4	4	[3,4,5]	[130, 90, 110]	110	110.0
5	5	[4,5,6]	[90, 110, 115]	105	105.0
6	6	[5,6,7]	[110, 115, 105]	110	110.0
7	7	[6,7]	[115, 105]	110	110.0
Exit	-	-	-	-	All rows processed

💡 All rows processed, moving averages calculated for each day using window frames.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	Final
Current Row (day)	N/A	1	2	3	4	5	6	7	7
Window Frame Rows	N/A	[1,2]	[1,2,3]	[2,3,4]	[3,4,5]	[4,5,6]	[5,6,7]	[6,7]	N/A
Values in Frame	N/A	[100,120]	[100,120,130]	[120,130,90]	[130,90,110]	[90,110,115]	[110,115,105]	[115,105]	N/A
Average Calculated	N/A	110	116.67	113.33	110	105	110	110	N/A
Output moving_avg	N/A	110.0	116.67	113.33	110.0	105.0	110.0	110.0	N/A

Key Moments - 3 Insights

Why does the window frame have fewer rows at the start and end?

At the start (day 1) and end (day 7), there are no preceding or following rows respectively, so the window frame includes only available rows, as shown in execution_table rows 1 and 7.

Why is the average for day 2 calculated over three rows but for day 1 over only two rows?

Because the window frame is defined as 1 preceding and 1 following row. Day 1 has no preceding row, so the frame includes itself and the 1 following row (day 2), while day 2 has both preceding (day 1) and following (day 3) rows, as seen in execution_table rows 1 and 2.

How does the ORDER BY clause affect the moving average calculation?

ORDER BY day ensures rows are processed in day order, so the window frame correctly includes adjacent days. Without ordering, the window frame would be meaningless, as shown in the execution_table where days are sequential.

Visual Quiz - 3 Questions

Look at the execution_table, what is the moving average output at step 3 (day 3)?

A 110.0

B 116.67

C 113.33

D 100.0

💡 Hint

Check the 'Output moving_avg' column at step 3 in the execution_table.

At which step does the window frame include only two rows instead of three?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Window Frame Rows' column in the execution_table for steps with fewer than three rows.

If the window frame was changed to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, how would the window frame at step 4 change?

A [2,3,4]

B [1,2,3,4]

C [2,3,4,5]

D [3,4]

💡 Hint

With 2 preceding rows and current row, the frame includes current and two before, check days before day 4.

Concept Snapshot

Moving averages with window frames:
Use AVG() OVER (ORDER BY col ROWS BETWEEN x PRECEDING AND y FOLLOWING)
Calculates average over a sliding window of rows
Window frame defines which rows to include
Handles edges by including only available rows
Results in smooth trend values per row

Full Transcript

This visual execution shows how moving averages are calculated in SQL using window frames. For each row, a window frame is defined around it, including a set number of preceding and following rows. The average of values in this frame is computed and assigned as the moving average for that row. The execution table traces each step, showing which rows are included in the frame and the calculated average. Variable tracking shows how the current row and window frame change over time. Key moments clarify common confusions about frame size at edges and the importance of ordering. The quiz tests understanding of the moving average values and window frame changes. This helps beginners see exactly how SQL computes moving averages step-by-step.

Practice

1. What does the SQL clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW do in a window function?

easy

A. It calculates the function over the current row and the two rows before it.

B. It calculates the function over the current row and the next two rows.

C. It calculates the function over all rows in the table.

D. It calculates the function only on the current row.

Solution

1. Step 1: Understand the window frame boundaries

   The clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the frame includes the current row and the two rows before it.

2. Step 2: Apply to window function calculation

   The window function (like AVG) will compute its result using values from these three rows.

3. Final Answer:

   It calculates the function over the current row and the two rows before it. -> Option A

4. Quick Check:

   Window frame includes current + 2 preceding rows = C [OK]

Hint: Preceding means rows before current, including current [OK]

Common Mistakes:

• Confusing PRECEDING with FOLLOWING
• Thinking it includes future rows
• Assuming it covers entire table

2. Which of the following is the correct syntax to calculate a 3-row moving average of the column sales ordered by date?

easy

A. SELECT date, AVG(sales) OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

B. SELECT date, AVG(sales) OVER (PARTITION BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

C. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS moving_avg FROM sales_data;

D. SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data;

Solution

1. Step 1: Identify correct window function syntax

   The correct syntax requires OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) to define ordering and frame.

2. 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.

3. Final Answer:

   SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data; -> Option D

4. Quick Check:

   ORDER BY + ROWS BETWEEN 2 PRECEDING AND CURRENT ROW = D [OK]

Hint: 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

3. Given the table sales_data with columns date and amount, what is the output of this query?

SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales_data ORDER BY date;

Assume data:
date | amount
2024-01-01 | 100
2024-01-02 | 200
2024-01-03 | 300

medium

A. [{"date": "2024-01-01", "amount": 100, "moving_avg": 150}, {"date": "2024-01-02", "amount": 200, "moving_avg": 200}, {"date": "2024-01-03", "amount": 300, "moving_avg": 300}]

B. [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}]

C. [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 200}, {"date": "2024-01-03", "amount": 300, "moving_avg": 300}]

D. [{"date": "2024-01-01", "amount": 100, "moving_avg": null}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}]

Solution

1. Step 1: Understand the window frame

   The frame includes the current row and 1 preceding row, so for each row, average the amount of that row and the previous one.

2. Step 2: Calculate moving averages row by row

   Row 1 (2024-01-01): only itself, avg = 100.
   Row 2 (2024-01-02): rows 1 and 2, avg = (100+200)/2 = 150.
   Row 3 (2024-01-03): rows 2 and 3, avg = (200+300)/2 = 250.

3. Final Answer:

   [{"date": "2024-01-01", "amount": 100, "moving_avg": 100}, {"date": "2024-01-02", "amount": 200, "moving_avg": 150}, {"date": "2024-01-03", "amount": 300, "moving_avg": 250}] -> Option B

4. Quick Check:

   Moving average includes current + 1 preceding row = A [OK]

Hint: Average current and previous row amounts for moving average [OK]

Common Mistakes:

• Including future rows instead of preceding
• Calculating average without window frame
• Assuming null for first row moving average

4. Identify the error in this SQL query for calculating a moving average:

SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND 2 PRECEDING) AS moving_avg FROM sales_data;

medium

A. AVG() cannot be used as a window function.

B. The ORDER BY clause is missing.

C. The window frame boundaries are in the wrong order; PRECEDING must come before CURRENT ROW.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check window frame syntax

   The frame clause ROWS BETWEEN CURRENT ROW AND 2 PRECEDING is invalid because the start boundary must be before the end boundary.

2. Step 2: Correct the order of boundaries

   The correct syntax is ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, with the smaller offset first.

3. Final Answer:

   The window frame boundaries are in the wrong order; PRECEDING must come before CURRENT ROW. -> Option C

4. Quick Check:

   Window frame start must be before end = A [OK]

Hint: Start frame boundary must be before end boundary [OK]

Common Mistakes:

• Reversing PRECEDING and CURRENT ROW order
• Thinking AVG() can't be window function
• Ignoring window frame syntax rules

5. 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?

hard

A. 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;

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

C. 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;

D. 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;

Solution

1. 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.

2. 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.

3. 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

4. Quick Check:

   Use CASE inside AVG to filter rows in window [OK]

Hint: 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