Month-Over-Month Growth in SQL (LAG Query)

The basic MoM 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, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0), 2
  ) AS mom_growth_pct
FROM monthly
ORDER BY month;

LAG(revenue, 1) looks back exactly one row — since the CTE has already collapsed the data to one row per month, one row back means one month back. NULLIF(..., 0) guards against a divide-by-zero error on a month that follows a genuine zero-revenue month.

Per-category month-over-month

WITH monthly AS (
  SELECT category, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1, 2
)
SELECT
  category, month, revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (PARTITION BY category ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (PARTITION BY category ORDER BY month), 0), 2
  ) AS mom_growth_pct
FROM monthly
ORDER BY category, month;

PARTITION BY category keeps each category's month-over-month comparison independent, so one category's January drop doesn't get compared against a different category's December.

The seasonality trap

MoM's biggest weakness is that it compares two calendar points that can be at very different places in a normal seasonal cycle. A retailer's November-to-December jump and December-to-January crash are both completely expected every single year — MoM reports both as dramatic swings, when neither one is actually news:

This is exactly the scenario year-over-year growth is built to handle better — comparing January 2026 to January 2025 controls for the seasonal pattern in a way January-vs-December never can. MoM is still useful for spotting sudden, non-seasonal shifts (a broken checkout flow, a sudden traffic drop), but it needs to be read alongside a sense of the business's normal seasonal shape, not in isolation.

The small-base volatility problem

-- A niche category going from 2 units to 5 units shows a 150% MoM increase —
-- mathematically correct, but not a meaningful signal on a base this small
SELECT category, month, units_sold,
  ROUND((units_sold - LAG(units_sold) OVER (PARTITION BY category ORDER BY month)) * 100.0
    / NULLIF(LAG(units_sold) OVER (PARTITION BY category ORDER BY month), 0), 2) AS mom_pct
FROM monthly_units;

Percentage change alone doesn't communicate scale. Showing the raw values alongside the percentage — as in every example above — lets a reader tell the difference between a genuinely large swing and a small, noisy category producing a dramatic-looking number.

Common mistakes

• Using MoM as the primary health metric for a seasonal business without a YoY comparison alongside it to control for the seasonal cycle.
• Reporting a MoM percentage without the underlying raw values, making a small-base swing look as significant as a large one.
• Forgetting NULLIF around the denominator when a prior month's value could legitimately be zero.
• Not partitioning by category/region when a per-segment MoM was actually needed, blending unrelated trends into one misleading number.