SQL Previous Row Comparison
LAG, applied to its most common real use: did this row go up or down from the last one?
What & Why
The Previous Row Comparison pattern is used to use LAG to compare the current row with its predecessor. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Growth wants daily active users compared with the prior observed 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 |
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 dau_change
FROM growth.daily_active_users
ORDER BY date_day;RESULT — compare with previous DAU
| date_day | dau | previous_dau | dau_change |
|---|---|---|---|
| 2024-04-01 | 1240 | NULL | NULL |
| 2024-04-02 | 1315 | 1240 | 75 |
| 2024-04-03 | 1288 | 1315 | -27 |
| 2024-04-04 | 1392 | 1288 | 104 |
LAG supplies the immediately preceding day's DAU.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario