How is your monthly revenue trending — and how does it compare to last year?

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date)            AS month,
    ROUND(SUM(total_amount)::numeric, 2)       AS revenue
  FROM orders
  WHERE order_date >= NOW() - INTERVAL '24 months'
    AND status NOT IN ('cancelled', 'refunded')
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  TO_CHAR(month, 'YYYY-MM')                   AS order_month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month)       AS prior_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month))
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0) * 100, 2
  )                                            AS mom_change_pct,
  LAG(revenue, 12) OVER (ORDER BY month)      AS prior_year_revenue,
  ROUND(
    (revenue - LAG(revenue, 12) OVER (ORDER BY month))
    / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 2
  )                                            AS yoy_change_pct
FROM monthly
ORDER BY month ASC