PatternSQL
Day-over-day change
Compare each day's value to the day before it with LAG(value) OVER (ORDER BY day), and handle the NULL on the very first row.
Last updated
Pattern
SELECT day, value, LAG(value) OVER (ORDER BY day) AS previous_value, value - LAG(value) OVER (ORDER BY day) AS day_over_day_change FROM daily_metrics ORDER BY day;
Why it’s written this way
LAG(value) OVER (ORDER BY day) looks one row back in the window's ordering and pulls that row's value alongside the current one, with no self-join needed. Subtracting it from the current value turns a column of daily totals into a column of day-over-day deltas in a single pass over the table.
What 'previous' means is entirely defined by the window's ORDER BY — it's the previous row in that ordering, which only lines up with 'yesterday's calendar date' when daily_metrics has exactly one row per consecutive day.
Edge cases to know
- →The first row in the ordering has nothing before it, so LAG returns NULL there and day_over_day_change is NULL too — that's the correct answer, not a bug, and it's worth leaving as NULL rather than coalescing it to a misleading 0.
- →A gap in the dates (a day with no row at all) makes LAG silently pull from the last row that does exist, so the computed 'change' actually spans however many calendar days were missing, not exactly one.
- →LAG(value, 2) OVER (...) shifts back two rows instead of one — the same pattern covers week-over-week or any other fixed offset just by changing that second argument.