Year-over-Year Growth in SQL

Example query

This example assumes: orders(id, order_date date, total_amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month,
         SUM(total_amount) AS sales
  FROM orders
  GROUP BY 1
)
SELECT cur.month,
       cur.sales,
       prev.sales AS sales_last_year,
       ROUND(100.0 * (cur.sales - prev.sales) / NULLIF(prev.sales, 0), 1) AS yoy_growth_pct
FROM monthly cur
LEFT JOIN monthly prev
  ON prev.month = (cur.month - INTERVAL '1 year')::date
ORDER BY cur.month;

How it works

• The same monthly CTE is joined to itself: each row is matched to the row exactly one year earlier.
• A LEFT JOIN keeps the first year, where there is no prior year, with nulls in the comparison columns.
• The growth is the difference divided by last year's figure; NULLIF guards against a zero base.

MySQL, BigQuery, SQL Server and SQLite differences

• A self-join on date arithmetic works everywhere; write the year offset as DATE_SUB(month, INTERVAL 1 YEAR) in MySQL, DATE_SUB(month, INTERVAL 1 YEAR) in BigQuery, or DATEADD(year, -1, month) in SQL Server.
• LAG(sales, 12) is a shortcut only if every month is present.

Common mistakes

• LAG(sales, 12) silently compares the wrong months if any month is missing; the self-join does not.
• Leap-year and partial-month effects distort February and the current month.
• A promotion or a price change last year makes the comparison base unusual; look at the level, not only the growth.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Compare each month's sales with the same month last year.
• What is our year-over-year growth for the last 6 months?