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 (ORDERBY day ROWS BETWEEN1 PRECEDING AND1 FOLLOWING) AS moving_avg
FROM sales_data
ORDERBY 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
Test your understanding
Look at the execution_table, what is the moving average output at step 3 (day 3)?
A110.0
B116.67
C113.33
D100.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?
AStep 2
BStep 1
CStep 3
DStep 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.