Month-over-month change in SQL with LAG (and the missing month)

Why the usual answer is wrong

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

LAG takes the previous row, and the row before April is February. April shows -300, a drop from February that nobody can compare: March is missing.

The answer

SELECT m.month, m.revenue, m.revenue - p.revenue AS change
FROM monthly_revenue m
LEFT JOIN monthly_revenue p
  ON p.month = strftime('%Y-%m', date(m.month || '-01', '-1 month'))
ORDER BY m.month;

Join each month to the calendar month before it. When that month has no row, the LEFT JOIN gives NULL and so does the change.

LAG is fine when every month has a row. In an interview, ask: can a month be missing? In PostgreSQL, use a date column and month - interval '1 month'.

SELECT month, revenue,
  CASE WHEN prev_month = strftime('%Y-%m', date(month || '-01', '-1 month'))
    THEN revenue - prev_revenue END AS change
FROM (
  SELECT month, revenue,
    LAG(month) OVER (ORDER BY month) AS prev_month,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
ORDER BY month;