SQL LAG
Read a previous ordered row without a self join.
What & Why
LAG reads a value from a prior row according to the window's partition and order. It makes backward-looking period comparisons, transitions, and elapsed gaps concise while preserving the original row grain.
See How It Works
BUSINESS QUESTION
Compare each daily active-user value with the previous 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 LAG read the previous DAU rowStep 1 of 4
LAG(dau) OVER (ORDER BY date_day)
| date_day | dau | previous_dau | change_from_previous |
|---|---|---|---|
| 2024-04-01 | 1240 | NULL | NULL |
| 2024-04-02 | 1315 | 1240 | +75 |
| 2024-04-03 | 1288 | 1315 | -27 |
| 2024-04-04 | 1392 | 1288 | +104 |
The first date has no previous row.
EXAMPLE QUERY
SELECT
date_day,
dau,
LAG(dau) OVER (ORDER BY date_day) AS previous_dau,
dau - LAG(dau) OVER (ORDER BY date_day) AS change_from_previous
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