SQL Year-over-Year Growth: Compare This Year vs Last Year (with CTEs)

The Problem

Your dashboard needs a "vs last year" column — revenue this January compared to last January. Sounds simple, but the classic mistake is joining the table to itself by MONTH(created_at) and getting a NULL for every month where last year had no data. I've debugged this exact query twice this quarter, so here's the clean way.

The LAG() Window Function Approach

WITH monthly AS (SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, SUM(amount) AS revenue FROM orders WHERE created_at >= '2024-01-01' GROUP BY 1) SELECT month, revenue, LAG(revenue, 12) OVER (ORDER BY month) AS last_year_revenue, ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month)) / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1) AS yoy_pct FROM monthly ORDER BY month;

The key trick is LAG(revenue, 12) — offset 12 rows back, which is exactly one year of monthly rows. No self-join, no YEAR()/MONTH() equality that silently drops NULL months.

Why Not a Self-Join?

A self-join ON YEAR(a.d) = YEAR(b.d) AND MONTH(a.d) = MONTH(b.d) works only if every month exists in both years. The moment January 2025 is missing, your February 2025 row loses its comparison partner and the report shows gaps. LAG() skips over missing rows by counting positions, not calendar dates — which is actually the more forgiving behavior for monthly aggregates.

Handling Missing Months (Calendar-Complete Version)

If you need a calendar-correct comparison, generate all months first: WITH RECURSIVE months AS (SELECT '2024-01-01' AS m UNION ALL SELECT DATE_ADD(m, INTERVAL 1 MONTH) FROM months WHERE m < '2026-06-01') ... then LEFT JOIN your aggregates onto it. You'll get 0s or NULLs for empty months and the LAG offset stays correct.

PostgreSQL Version

Same shape, different date function: WITH monthly AS (SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue FROM orders WHERE created_at >= '2024-01-01' GROUP BY 1) SELECT month, revenue, LAG(revenue, 12) OVER (ORDER BY month) AS last_year_revenue, ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month)) / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1) AS yoy_pct FROM monthly ORDER BY month;

Quick Checklist

1. Aggregate to monthly granularity first. 2. Use LAG(column, 12) — not 1 — for YoY on monthly data. 3. Wrap the denominator in NULLIF to avoid divide-by-zero. 4. Filter the source table to two years of data so the 12-row offset is correct. 5. If months can go missing, LEFT JOIN against a generated calendar.

Try It Yourself

Paste "compare revenue this year vs last year by month" into our free SQL generator and pick MySQL or PostgreSQL — it'll emit the full query with your own table name.