Period-Over-Period Change
Computing month-over-month growth and day-over-day deltas with LAG.
The Guaranteed Analyst Question
If you interview for a data analyst role, expect: "Compute month-over-month growth" or "What's the day-over-day change?" It is nearly unavoidable.
The building block is LAG: grab the prior period's value, then compute an absolute delta or a percentage change. This lesson turns LAG into the period-over-period patterns interviewers grade you on.
Absolute Change With LAG
The simplest version is the raw difference between this period and the last. Pull the previous value with LAG and subtract it inline.
The first ordered row has no predecessor, so its change is NULL. That is correct, not a bug: there is genuinely no prior period to compare against.
SELECT
month,
revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales
ORDER BY month;All lessons in this course
- LAG and LEAD for Adjacent Rows
- Period-Over-Period Change
- NTILE for Bucketing
- FIRST_VALUE, LAST_VALUE and Frame Edges