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, andLAG(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. LEADis the forward-looking twin.