SQL SUM OVER
Add values across a window without grouping away the detail rows.
What & Why
SUM(...) OVER (...) returns a windowed total beside each input row instead of collapsing the rows. It can show a full total and an ordered cumulative total while preserving each date that contributed to them.
See How It Works
BUSINESS QUESTION
Compare the full new-user total with the running total reached on each day.
| 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 full-window and cumulative sums coexistStep 1 of 4
SUM(new_users) OVER () · SUM(new_users) OVER (ORDER BY date_day)
| date_day | new_users | total_new_users | running_new_users |
|---|---|---|---|
| 2024-04-01 | 180 | 770 | 180 |
| 2024-04-02 | 205 | 770 | 385 |
| 2024-04-03 | 164 | 770 | 549 |
| 2024-04-04 | 221 | 770 | 770 |
The full-window total is 770; the running frame starts at 180.
EXAMPLE QUERY
SELECT
date_day,
new_users,
SUM(new_users) OVER () AS total_new_users,
SUM(new_users) OVER (
ORDER BY date_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_new_users
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