Aggregate to one row per month first. Then use LAG to bring in the previous month and compute the change as a share of the previous value.
Query
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS mom_pct
FROM monthly
ORDER BY month;Example output:
month revenue prev mom_pct 2025-01 100000 NULL NULL 2025-02 120000 100000 20.0 2025-03 108000 120000 -10.0
The first month has no previous month, so NULL is the honest value. NULLIF(..., 0) stops a divide-by-zero when the previous month had no revenue.
The trap: missing months
LAG looks at the previous row, not the previous month. If you had no sales in April, there is no April row. May's "previous" row then becomes March, and the result calls it month-over-month growth when it really compares two months apart. This happens in new or seasonal products.
Fix it by joining the data onto a calendar of months, so every month exists:
SELECT m.month, COALESCE(x.revenue, 0) AS revenue FROM dim_month m LEFT JOIN monthly x ON x.month = m.month
Then apply LAG on that. With a zero month, the next month's growth divides by zero, and the NULLIF returns NULL. Decide how the report should display that, because "infinite growth" is not a number anyone wants.
Year-over-year
Use LAG(revenue, 12) on the filled calendar. On a gappy table, a self-join on month = prev_month + 12 months is safer.
Also mention that partial months distort the number. Compare a full March to a half-finished April and growth looks like a collapse. Either exclude the current month or label it.