SQL Moving Average
A fixed-size window of AVG instead of SUM — smooths out noise instead of accumulating.
What & Why
A moving average applies AVG over a sliding, fixed-size frame — exactly the moving window frame mechanic from the previous lesson group, now used for its most common real purpose: smoothing out noisy data.
See How It Works
BUSINESS QUESTION
Calculate a three-row moving average of daily active users.
| date_day | dau | new_users | returning_users |
|---|---|---|---|
| 2024-04-01 | 1240 | 180 | 1060 |
| 2024-04-02 | 1315 | 205 | 1110 |
| 2024-04-03 | 1288 | 164 | 1124 |
| 2024-04-04 | 1392 | 221 | 1171 |
Watch a three-row moving average advanceStep 1 of 4
AVG(dau) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
| date_day | dau | frame rows | moving_3_row_avg |
|---|---|---|---|
| 2024-04-01 | 1240 | 1240 | 1240.00 |
| 2024-04-02 | 1315 | 1240, 1315 | 1277.50 |
| 2024-04-03 | 1288 | 1240, 1315, 1288 | 1281.00 |
| 2024-04-04 | 1392 | 1315, 1288, 1392 | 1331.67 |
The first frame uses the one available observation.
EXAMPLE QUERY
SELECT
date_day,
dau,
ROUND(AVG(dau) OVER (
ORDER BY date_day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_3_row_avg
FROM growth.daily_active_users
ORDER BY date_day;This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario