Month-over-month growth in SQL with LAG() — and why a self-join is the wrong tool

"Compare each month to the month before it" — revenue growth, week-over-week signups, day-over-day active users — is a comparison-to-the-previous-row question, and it's one of the clearest cases where reaching for a join is a sign to stop and reach for a window function instead.

The instinct to avoid: self-join

SELECT curr.month, curr.revenue, prev.revenue AS prev_revenue
FROM monthly_revenue curr
LEFT JOIN monthly_revenue prev
  ON prev.month = curr.month - INTERVAL '1 month';

This can work, but it's fragile in a specific way: it depends on curr.month - INTERVAL '1 month' landing exactly on another row's month value. The moment there's a gap — a month with zero revenue that was never inserted, a week that had no signups at all — the join silently returns no match for that pair and prev_revenue is NULL when it shouldn't be, with no error to flag it. It's also more code than the problem needs.

The pattern: LAG()

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly_revenue
ORDER BY month;

LAG(revenue) reaches back to the previous row in the ordering the window sees, not the previous calendar month — the distinction matters. If a row is genuinely missing from the table, LAG still returns the value from whatever row is immediately before it in sort order, silently skipping the gap rather than erroring on it. That's usually the more useful behavior ("revenue compared to the last month we have data for") but it's worth stating explicitly which one the interviewer wants, because the two answers differ the moment there's a gap in the data.

LEAD() is the mirror image — same syntax, reaches forward to the next row instead of back — useful for "what happens after this event" questions.

The edge case interviewers actually check: the first row

There is no month before the first one, so LAG(revenue) OVER (ORDER BY month) returns NULL for it, and that's correct — a leading NULL is honest that no prior value exists. Most candidates get the main query right and then get asked "what happens on row one?" as a follow-up. Two answers are wrong: assuming a 0 (implies zero revenue, not missing data — different claim) and being surprised the value is NULL at all. The right answer is stating it directly: "the first row has no predecessor, so it's NULL, and if the query needs a number instead, that's a COALESCE(LAG(revenue) OVER (...), 0) decision, not a LAG decision."

Turning the value into growth

Once prev_revenue exists as a column, month-over-month growth is arithmetic on top of it, no new window function needed:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(
         (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month) * 100, 1
       ) AS pct_change
FROM monthly_revenue
ORDER BY month;

One real trap here: if any of the operands are declared as an integer type rather than NUMERIC, revenue - LAG(...)) / LAG(...) performs integer division and truncates the result to 0 for any growth under 100%, not a rounding error but a silently wrong number. Cast to NUMERIC (or seed the column as NUMERIC to begin with) before dividing.

More in Window functions.