Skip to content

Window functions · Medium

How would you compute month-over-month growth using LAG?

Short answer

Aggregate to one row per month, then use LAG(revenue) OVER (ORDER BY month) to bring the previous month's value onto the current row. Growth is (current − previous) ÷ previous. Guard against division by zero with NULLIF, and remember the first month has no previous value, so its growth is NULL rather than zero.

Detailed answer

WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date)  AS month,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status NOT IN ('cancelled', 'returned')
  GROUP BY month
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_growth_pct
FROM monthly
ORDER BY month;

Points worth raising:

  • LAG(x, n) looks back n rows, and LAG(x, 1, 0) supplies a default — but defaulting the previous value to 0 would then cause a divide-by-zero, so leave it NULL.
  • LAG looks at the previous row, not the previous calendar month. If a month had no orders it is missing from monthly, and LAG would compare against two months earlier. The robust fix is to generate a complete month list (a recursive CTE or calendar table) and LEFT JOIN revenue onto it.
  • LEAD is the forward-looking twin.