SQL Compare Consecutive Rows
SQL Compare Consecutive Rows helps analysts compare neighboring ordered rows with LAG.
What & Why
Interviewers often phrase this as "find rows where the value changed from the previous row" — that's just LAG plus a comparison, already covered in the Window Functions series, worth recognizing under this framing too.
See How It Works
BUSINESS QUESTION
Growth wants the change in daily active users between consecutive observations.
| 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 prior_dau,
dau - LAG(dau) OVER (ORDER BY date_day) AS absolute_change
FROM growth.daily_active_users
ORDER BY date_day;RESULT — consecutive DAU comparison
| date_day | dau | prior_dau | absolute_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 aligns every day with the previous representative row.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario