Level 4 · Month-over-month growth

LAG(x) returns the value of x from the previous row in the window’s order. LEAD(x) returns the next row’s. They make period-over-period comparisons trivial:

SELECT
  month,
  net_revenue,
  LAG(net_revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly;

Growth is then simple arithmetic: (net_revenue - prev_revenue) / prev_revenue * 100.

Filter after the window, not before

This is the trap in Megan’s request. If the monthly CTE only contained 2025, January would have no previous row and prev_revenue would be NULL. Window functions only see rows that survive WHERE. So:

1. Build monthly revenue for all months.
2. Compute LAG over all of them.
3. Filter to 2025 in the outer query.

January 2025 then correctly compares with December 2024. The same principle applies to running totals, moving averages and ranks: filtering early changes what the window sees.

Options for LAG

LAG(net_revenue, 12) OVER (ORDER BY month)     -- same month last year (year-over-year)
LAG(net_revenue, 1, 0) OVER (ORDER BY month)   -- default 0 instead of NULL
LEAD(net_revenue) OVER (ORDER BY month)        -- next month

LAG(x, 12) for year-over-year only works if every month is present. LAG counts rows, not calendar months: if a month is missing, “12 rows back” is 13 months back. For sparse data, join to a generated calendar first (chapter 6), or join each month to month - INTERVAL 1 YEAR instead.

Avoid the 0 default for growth calculations. Dividing by a fake zero is an error or infinity, and a NULL growth for the first period is the honest answer.

Per group

Add PARTITION BY to compare within each group, for example each customer’s days since their previous order:

CAST(order_date AS DATE)
  - LAG(CAST(order_date AS DATE)) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_prev

Seasonality

Month-over-month growth is noisy for a seasonal business. Harbor & Pine sells far more in November and December, so January always looks like a collapse next to December. That is why finance teams often prefer year-over-year growth for the same month, which compares like with like.

Percent vs percentage points

If the conversion rate goes from 2.0% to 2.5%, that is +0.5 percentage points, or +25% relative growth. Say which one you mean in a report; people mix them up constantly.

Interview note

“Calculate month-over-month growth” is a standard analyst interview question. Strong answers aggregate to months in a CTE, use LAG, explain what happens to the first month, and mention that LAG counts rows, so missing months need a calendar.