AdvancedAdvanced PostgreSQL

WINDOW: compare each month to previous month (MoM growth)

The query

SQL

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date)::DATE AS month,
    SUM(total) AS revenue
  FROM orders WHERE status = 'completed'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
  ) AS mom_growth_pct
FROM monthly
ORDER BY month;

Tested against PostgreSQL 16

Note

“MoM (Month-over-Month) growth calculation using LAG window function. NULLIF prevents division by zero.

Tables referenced

orders6 cols