SQL UNBOUNDED PRECEDING
The frame boundary that means 'all the way to the start of the partition.'
What & Why
UNBOUNDED PRECEDING as a frame's starting boundary means "don't stop early — include every row from the very first row of the partition onward." It's what makes a running total actually accumulate everything, instead of just a fixed number of nearby rows.
See How It Works
BUSINESS QUESTION
Growth wants cumulative new users from the beginning of the series.
| 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,
new_users,
SUM(new_users) OVER (ORDER BY date_day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_new_users
FROM growth.daily_active_users
ORDER BY date_day;RESULT — cumulative new users
| date_day | new_users | cumulative_new_users |
|---|---|---|
| 2024-04-01 | 180 | 180 |
| 2024-04-02 | 205 | 385 |
| 2024-04-03 | 164 | 549 |
| 2024-04-04 | 221 | 770 |
UNBOUNDED PRECEDING starts every frame at the first day.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario