How do you calculate year-over-year or month-over-month growth in SQL?

Q: How do you calculate year-over-year or month-over-month growth in SQL?

**Target Audience:** Beginners who can write `GROUP BY` and want to compare one period with the one before it, and candidates preparing for analytics interviews. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. `LAG()` is standard SQL and also exists in MySQL 8.0+ and SQL Server; the date functions differ per engine and are named where used. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You keep a notebook of your monthly savings. To know whether you are doing better, you look at this month, glance at the line above (last month), and do a quick sum: "I saved 125 this mont

The problem

Question presented to candidate: "Finance wants a report: for each month, revenue, the change versus the previous month as a percentage, and the change versus the same month last year. How would you write that in SQL, and what can go wrong with the numbers?"

What a strong answer should cover:

The formula: growth % = (current - previous) / previous × 100, with NULLIF(previous, 0) so a zero base gives NULL instead of an error
Month over month with LAG(revenue) OVER (ORDER BY month): the previous row, computed in a CTE, then the arithmetic in the outer query
Year over year either with LAG(revenue, 12) (only safe when every month has a row) or, more robustly, a self join on "same month, previous year"
Missing months: LAG returns the previous row, not the previous calendar month; fill a calendar (generate_series in PostgreSQL, a recursive CTE elsewhere) or check that the months are adjacent
Arithmetic traps: integer division truncates (100 * 8000 / 104000 is 7), PostgreSQL raises division by zero while SQLite returns NULL, and the first period has no base (NULL, not 0 %)

Clarifying questions expected:

Is the data already one row per month, or do I aggregate from raw orders (and in which time zone are the dates)?
What should a month with no sales show: 0 revenue, or no row?
Should a partial current month be compared with a full previous month?

Code / implementation expected: Yes — a LAG-based month-over-month query and a year-over-year comparison, with the division made safe.

Schema used by the examples:

window-functions
analytics

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who can write GROUP BY and want to compare one period with the one before it, and candidates preparing for analytics interviews. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. LAG() is standard SQL and also exists in MySQL 8.0+ and SQL Server; the date functions differ per engine and are named where used.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You keep a notebook of your monthly savings. To know whether you are doing better, you l

The solution is hidden so you can try first

14 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLMonth over month with LAG(), computed in a CTE and made safe with NULLIF

SQLYear over year: join each month to the same month of the previous year

SQLThe missing-month trap: LAG compares July with May unless you check adjacency

PostgreSQL: fill a calendar with generate_series, then LAG over the full month list

WITH calendar AS (
  SELECT to_char(d, 'YYYY-MM') AS month
  FROM generate_series(DATE '2024-03-01', DATE '2024-08-01', INTERVAL '1 month') AS g(d)
), filled AS (
  SELECT c.month, r.revenue,
         LAG(r.revenue) OVER (ORDER BY c.month) AS prev_revenue
  FROM calendar AS c
  LEFT JOIN monthly_revenue AS r ON r.month = c.month
)
SELECT month, revenue, prev_revenue,
       ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS mom_pct
FROM filled
WHERE month >= '2024-04'
ORDER BY month;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.