Advanced · Lesson 13 of 14

LAG, LEAD, running totals

Compare a row to other rows in time.

Looking backward and forward

LAG(col) OVER (ORDER BY month) returns the value from the previous row, LEAD from the next. This is the direct route to month over month growth.

Growth formulas

Growth is (current - previous) / previous, times 100 for a percent. Wrap the denominator in NULLIF to avoid division by zero.

Partition resets comparisons

PARTITION BY account_id makes LAG compare each account to itself across time, which is normally exactly what you want.

Examples

Previous value with LAG

Each month beside the one before it.

SELECT account_id, month, mrr, LAG(mrr) OVER (PARTITION BY account_id ORDER BY month) AS prev_mrr FROM monthly_revenue;

Percent growth

Safe growth calculation, nulls handled.

WITH p AS (SELECT account_id, month, mrr, LAG(mrr) OVER (PARTITION BY account_id ORDER BY month) AS prev FROM monthly_revenue) SELECT account_id, month, ROUND(100.0 * (mrr - prev) / NULLIF(prev, 0), 2) AS growth_pct FROM p WHERE prev IS NOT NULL ORDER BY account_id, month;