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
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.
Part by part
- LAG(value) OVER (ORDER BY day)
- Reads the value from the previous row in the given order, without a self-join. The ordering is what defines which row counts as previous.
- value - LAG(value) OVER (ORDER BY day)
- The change itself. Repeating the window expression is normal; the planner evaluates it once, and a CTE gives it a name if it appears more than twice.
Common mistakes
LAG(value) OVER ()Without an ORDER BY the window has no defined sequence, so which row is previous is arbitrary and can change between runs. The result looks plausible and is not reproducible.
LAG(value) OVER (ORDER BY day)LAG(value) OVER (ORDER BY day) with gaps in the seriesLAG takes the previous ROW, not the previous day, so a missing day makes the comparison span two days without saying so. Fill the series first if the interval has to be uniform.
Comparing against LAG without handling the first rowThe first row has no predecessor, so LAG returns NULL and the subtraction yields NULL. That is correct, and it means any downstream SUM or AVG silently skips it.
Questions
What does the first row return?
NULL, because there is nothing before it. Pass a third argument to LAG for a default — LAG(value, 1, 0) yields zero — but consider whether zero change or no data is the honest claim.
How do I get the change per group?
Add a partition: LAG(value) OVER (PARTITION BY metric ORDER BY day). Without it the window runs across every group and the first row of each is compared to the last row of the previous one.
What is LEAD for?
The mirror image — it reads forward instead of back. Useful for computing the gap until the next event, or for a centred comparison where both neighbours matter.
How do I express the change as a percentage?
Divide by the lagged value and guard against zero with NULLIF, since a previous value of zero has no meaningful percentage change. The same caveat applies as in any percent-change calculation.