SQL Rolling Average
Usually means a TRAILING average — only rows before and including the current one, not centered.
What & Why
"Rolling average" often specifically implies a trailing window — the current row plus a fixed number of rows before it, never after. This is the natural choice whenever "after" doesn't exist yet, like a live dashboard showing trailing performance.
See How It Works
BUSINESS QUESTION
Growth wants a seven-observation rolling 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 |
EXAMPLE QUERY
SELECT
date_day,
dau,
ROUND(AVG(dau) OVER (ORDER BY date_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS rolling_7_row_avg
FROM growth.daily_active_users
ORDER BY date_day;RESULT — rolling DAU average
| date_day | dau | rolling_7_row_avg |
|---|---|---|
| 2024-04-01 | 1240 | 1240.00 |
| 2024-04-02 | 1315 | 1277.50 |
| 2024-04-03 | 1288 | 1281.00 |
| 2024-04-04 | 1392 | 1308.75 |
The seven-row frame uses all rows available so far in this four-row sample.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario