Using LAG, how do you compute each month's percent change over the previous month?

## The canonical query ```sql SELECT region, month, revenue, revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS delta, 100.0 * (revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY month)) / NULLIF(LAG(revenue) OVER (PARTITION BY region ORDER BY month), 0) AS pct_change FROM monthly_revenue; ``` This is the highest-frequency practical use of `LAG` and appears constantly in analytics interviews as "show month-over-month growth". Every element of it is there for a reason. ## Why PARTITION BY The table almost always holds several interleaved series — one row per region per month, one per product per day. Without `PARTITION BY region`, the window is one long ordered list, and the earliest month of the second region reads the latest month of the first. The result is not an error; it is a plausible-looking wrong number, which is worse. Partition by exactly the columns that identify one series. ## Why NULLIF A previous period of zero makes the ratio undefined. Most engines raise a division-by-zero error rather than returning infinity, so one zero month aborts the whole query. `NULLIF(x, 0)` returns NULL when `x` is 0 and `x` otherwise; dividing by NULL yields NULL, which propagates harmlessly and reads as "not computable". This is the standard, portable guard. ## Why 100.0 rather than 100 If `revenue` is an integer type, `(a - b) / b` is integer division: a 40% rise computes as `40 / 100` = `0`. Multiplying by `100.0` first, or casting one operand to a decimal type, keeps the arithmetic exact-numeric or approximate rather than truncating. This bites people who test with a nicely rounded fixture and never see the truncation. ## Why the first row should stay NULL `LAG(revenue)` returns NULL for the first month of each partition, so `delta` and `pct_change` are NULL there. That is the correct answer — no prior period exists. The tempting `LAG(revenue, 1, 0)` makes `delta` equal the whole first month's revenue (reported as growth from nothing) and turns the denominator into 0. Leave the default as NULL and let the consumer decide how to render it. ## The gap trap `LAG` returns the previous **row**, not the previous **calendar month**. If a region has no row for March, April's `LAG` reaches back to February and the query silently labels a two-month change as month-over-month. Two honest responses: state the assumption that the series is dense, or join against a generated calendar of periods first so that missing periods exist as rows before the window runs. ## Filtering on the result Window functions are evaluated after `WHERE`, `GROUP BY` and `HAVING`, so `WHERE pct_change < -10` in the same select is invalid. Wrap it: ```sql WITH mom AS ( SELECT region, month, revenue, LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev FROM monthly_revenue ) SELECT region, month, 100.0 * (revenue - prev) / NULLIF(prev, 0) AS pct_change FROM mom WHERE prev IS NOT NULL AND revenue < prev; ``` Computing `prev` once in the CTE also removes the repetition of the `LAG` expression, which is the readability win of this shape. ## Year-over-year and other offsets On a dense monthly series, `LAG(revenue, 12)` gives the same month a year earlier — again dependent on the series having no gaps. When the data is not dense, prefer a self-join on an explicit date arithmetic predicate, or densify first. On a daily series, `LAG(value, 7)` gives the same weekday a week earlier, which is often more meaningful than day-over-day for weekly-seasonal data. ## What interviewers listen for The query itself is table stakes; the differentiators are naming the partitioning column without prompting, guarding the denominator, keeping the first row NULL, and volunteering that `LAG` counts rows rather than calendar periods.