Year Over Year Growth SQL Query (YoY With LAG)

Prerequisite: one clean row per month

Every version of this query assumes the data is already aggregated to one row per period, with no gaps. Get there first:

SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

The core LAG pattern

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

NULLIF(..., 0) guards against a divide-by-zero error if the prior year's revenue happened to be exactly zero — the growth percentage becomes NULL instead of erroring the whole query out.

Alternative: explicit self-join

When the comparison period isn't a clean fixed offset — or the query needs to be more self-documenting for people unfamiliar with LAG — a self-join on the calendar year expresses the same intent more explicitly:

SELECT
  curr.month,
  curr.revenue,
  prior.revenue AS revenue_prior_year,
  ROUND((curr.revenue - prior.revenue) * 100.0 / NULLIF(prior.revenue, 0), 1) AS yoy_growth_pct
FROM monthly curr
LEFT JOIN monthly prior
  ON prior.month = curr.month - INTERVAL '1 year'
ORDER BY curr.month;

Functionally equivalent to the LAG version when the data has no gaps — but this version stays correct even if a month is missing, since it joins on the actual calendar date rather than counting rows back.

The incomplete-period trap

The single most common YoY mistake: comparing a month that's still in progress against a prior year's complete month. Pulled on the 12th of the current month, this shows a steep "decline" that's entirely an artifact of the comparison, not real performance:

Raw comparison says -55% YoY. But 18,000 over 12 days is actually ahead of last year's pace (40,000 ÷ 30 days × 12 days ≈ 16,000 expected). Two fixes, either of which works:

• Exclude the current, still-in-progress period from the YoY chart entirely until it closes.
• Normalize both periods to the same number of elapsed days before comparing — divide each month's revenue by its days-elapsed-so-far and compare the daily rate instead of the raw total.

Month-over-month and quarter-over-quarter

Same exact pattern, different offset:

Handling missing months

If a month had zero orders, it won't appear as a row in a GROUP BY-aggregated table at all — and a missing row silently shifts every LAG(..., 12) comparison after it by one position. Generate a complete calendar spine first and LEFT JOIN actual data onto it so every month exists, even with a revenue of zero:

WITH months AS (
  SELECT GENERATE_SERIES(
    '2024-01-01'::DATE, '2026-06-01'::DATE, INTERVAL '1 month'
  ) AS month
)
SELECT m.month, COALESCE(SUM(o.amount), 0) AS revenue
FROM months m
LEFT JOIN orders o ON DATE_TRUNC('month', o.order_date) = m.month
GROUP BY m.month
ORDER BY m.month;

Common mistakes

• Comparing an in-progress period against a complete prior one — the single biggest source of misleading YoY charts.
• Assuming LAG's row offset always equals a calendar year when the underlying data has missing months — it doesn't; row position and calendar time silently diverge.
• No NULLIF guard on the division, causing a hard error whenever a prior-year value happens to be zero.
• Mixing timezone-naive and timezone-aware dates across the current and prior year's data, which can shift a handful of orders across the month boundary and skew both totals slightly.