Deviopath › PostgreSQL

Month-over-Month Growth

Hard · Window Functions

LAG(x) OVER (ORDER BY ...) reads a value from the previous row. It is the usual tool for "compared with last month" numbers. Careful: the previous row is not always the previous calendar month, because months without sales have no row at all.

Only orders with status = 'paid' count. For every month that has paid orders, return:

- month: the month as text YYYY-MM (use to_char)

- revenue: the sum of amount

- growth_pct: ROUND(100 * (revenue - previous) / previous, 1), where previous is the revenue of the previous calendar month. If the previous calendar month had no paid orders (or this is the first month), growth_pct is NULL.

Sort by month.

Next: 7-Day Moving Average