All lessons

Module 8: Patterns analysts use every week · Lesson 8.1 · about 30 min

Month-over-month and year-over-year growth

Turn orders into a monthly revenue table, then use LAG to work out month-over-month and year-over-year growth, without the traps of missing months and integer division.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 8 · about 30 minutes · read first: 6.4, 7.1. You’ll be able to build a monthly revenue table and add month-over-month (MoM) and year-over-year (YoY) growth columns that are correct even when months are missing.

Growth compares a number with the same number one period earlier: make one row per month, then let LAG fetch the earlier value.

In everyday terms: checking your weight on the same scale every month. “Up 2 kg since last month” is month-over-month; “up 3 kg since this time last year” is year-over-year, and it ignores the extra holiday dinners in December.

1. Why this matters

“How did we do compared with last month, and compared with last year?” is the first question in almost every sales meeting. The query behind it is one of the most-written queries in analyst jobs, and it turns up in interviews all the time. It mixes three skills you already have: grouping by month, window functions and careful arithmetic.

2. Step one: one row per month

Growth compares a number with an earlier number of the same kind. So first make a table with exactly one row per month. On Northbay, revenue is the value of the lines in completed orders: quantity × unit price × (1 − discount).

-- try
SELECT strftime('%Y-%m', o.order_date) AS month,
       ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount)), 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY month
ORDER BY month

You get 24 rows, January 2024 to December 2025. strftime('%Y-%m', ...) turns a date into its month, like 2025-12. (SQL Server: FORMAT(order_date, 'yyyy-MM') or DATETRUNC(month, order_date); PostgreSQL: date_trunc('month', order_date).)

3. Month-over-month growth with LAG

Month-over-month growth is the change since the month before, as a share of the month before. LAG(revenue) fetches the previous row’s revenue (Lesson 6.4), so put the monthly table in a CTE and add the window on top.

-- try
WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(i.quantity * i.unit_price * (1 - i.discount)) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY month
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       ROUND(LAG(revenue) OVER (ORDER BY month), 2) AS prev_month,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct
FROM monthly
ORDER BY month

The first month has no month before it, so prev_month and mom_pct are NULL. That is correct: there is nothing to compare with. Notice the swings: February 2024 is down 31.9% (a short, quiet month after the January sale), and November 2024 is up 40.1% (Black Friday).

Two habits keep this arithmetic safe:

  • Write 100.0, not 100. In SQLite, SQL Server and PostgreSQL, an integer divided by an integer drops the fraction, so a 25% rise can come out as 0.
  • Wrap the divisor in NULLIF(x, 0). If last month was 0, you get NULL instead of an error or a meaningless infinity.

4. Year-over-year growth

Monthly numbers swing with the seasons: December is always busy. Year-over-year growth compares a month with the same month a year earlier, which cancels out the season. In a table with one row per month, a year earlier is 12 rows back: LAG(revenue, 12).

-- try
WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(i.quantity * i.unit_price * (1 - i.discount)) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY month
)
SELECT month,
       ROUND(revenue, 2) AS revenue,
       ROUND(LAG(revenue, 12) OVER (ORDER BY month), 2) AS same_month_last_year,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS yoy_pct
FROM monthly
ORDER BY month

All of 2024 shows NULL for YoY, because the data starts in January 2024. Every month of 2025 grew on the year before, from +8.3% in September to +94.2% in February.

Maths and principle

Principle: growth is a ratio of two values of the same measure at two times. Before you can take the ratio, the data must be indexed by time with exactly one value per period.

Equation: g(t) = (x(t) − x(t−k)) / x(t−k), with k = 1 for MoM and k = 12 for YoY on monthly data. In relational algebra the monthly table is γmonth; SUM(value)(σstatus=‘completed’(orders ⋈ order_items)). LAG then reads x(t−k) by position in the ordered result, so it is only right if position = time, which needs every month present.

Analogy: comparing your height with your height “two lines up” on a growth chart. If someone skipped a year on the chart, two lines up is no longer two years ago.

Try it: growth compounds. Two months of +10% is 1.1 × 1.1 = 1.21, so +21%, not +20%. What do +50% then −50% give? (1.5 × 0.5 = 0.75: a 25% fall.)

Month-over-month (MoM)
The change since the month before, divided by the month before.
Year-over-year (YoY)
The change since the same month a year earlier. It cancels out the seasons.
LAG(x, k) OVER (ORDER BY month)
The value of x from k rows earlier in month order. It counts rows, not months.
NULLIF(x, 0)
Turns a 0 into NULL so that dividing by it gives NULL instead of an error.
Calendar table
A list of every month (or day), used so that empty months still get a row.
Physical meaning
The query first squashes thousands of order lines into one row per month. LAG then copies the value from the row above into the current row, so the new and old numbers sit side by side and one division gives the growth.

“LAG(revenue) always gives last month.” It gives the previous row. If a month had no sales, it has no row, and LAG reaches back two months without telling you. Join a calendar of every month first, and turn missing values into 0 with COALESCE.

Watch it happen, one step at a time:

One row per month. GROUP BY month gives the monthly table.
monthrevenue
2025-014000
2025-025000
2025-034500
LAG copies the row above.
monthrevenueprev
2025-014000NULL
2025-0250004000
2025-0345005000
Divide by the older number. (5000 − 4000) ÷ 4000 = 25 %; (4500 − 5000) ÷ 5000 = −10 %.
monthrevenuemom_pct
2025-014000NULL
2025-02500025.0
2025-034500−10.0
The trap. If April has no sales, May sits right under March, and LAG compares May with March.
monthsoldprev
2025-0364
2025-0536 (March!)
The fix. LEFT JOIN onto a calendar of every month and use COALESCE(sold, 0).
monthsoldprev
2025-0364
2025-0406
2025-0530

Any live box with a SELECT has a Show how it runs button that animates the real query on Northbay, row by row.

5. The missing-month trap

Company revenue has a row for every month, but smaller slices often do not. Northbay sold no duvets in April or May 2024, so in a per-product monthly table, the row before June 2024 is March 2024. LAG would quietly call that “last month”.

-- try
SELECT strftime('%Y-%m', o.order_date) AS month,
       SUM(i.quantity) AS duvets
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed' AND i.product_id = 43
GROUP BY month
ORDER BY month

Only 17 rows instead of 24. The fix: build a calendar of every month with a recursive CTE (Lesson 5.5), LEFT JOIN the sales onto it and turn missing sales into 0.

-- try
WITH RECURSIVE months(m) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT date(m, '+1 month') FROM months WHERE m < '2025-12-01'
),
duvets AS (
  SELECT strftime('%Y-%m', o.order_date) AS month, SUM(i.quantity) AS sold
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed' AND i.product_id = 43
  GROUP BY month
)
SELECT strftime('%Y-%m', months.m) AS month,
       COALESCE(d.sold, 0) AS sold,
       LAG(COALESCE(d.sold, 0)) OVER (ORDER BY months.m) AS prev_month
FROM months
LEFT JOIN duvets d ON d.month = strftime('%Y-%m', months.m)
ORDER BY month

Now June 2024 is compared with May 2024 (0 sold), as it should be. Growth from 0 is undefined, which is exactly why you use NULLIF in the divisor.

6. Worked example

Question: how did December 2025 do?

  1. Monthly table (section 2): December 2025 revenue = $9,136.83.
  2. Month before: November 2025 = $7,039.58. MoM = (9,136.83 − 7,039.58) / 7,039.58 = +29.8%.
  3. Same month last year: December 2024 = $6,378.04. YoY = (9,136.83 − 6,378.04) / 6,378.04 = +43.3%.
  4. For the whole year, group by year instead: 2025 = $72,594.52, 2024 = $53,985.46, so +34.5%.

The one-line insight for a manager: “December was our best month ever, up 43% on last December; the whole year grew 34.5%.” Notice that the MoM figure on its own (+29.8%) mostly reflects the holiday season, which is why YoY is the fairer headline.

7. Practice

-- exercise: Show each month with its number of completed orders and the month-over-month change in orders as a percentage (1 decimal place), in month order.
-- hint: Count orders per month in a CTE, then use LAG(orders) OVER (ORDER BY month). Remember 100.0.
SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month
-- answer:
WITH m AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE status = 'completed'
  GROUP BY month
)
SELECT month, orders,
       ROUND(100.0 * (orders - LAG(orders) OVER (ORDER BY month))
             / LAG(orders) OVER (ORDER BY month), 1) AS mom_pct
FROM m
ORDER BY month
-- exercise: Work out year-over-year revenue growth for each channel (store and online): one row per channel with 2024 revenue, 2025 revenue and growth in % to 1 decimal place.
-- hint: Use SUM(CASE WHEN strftime('%Y', o.order_date) = '2024' THEN ... END) for each year, grouped by channel.
SELECT o.channel,
       ROUND(SUM(i.quantity * i.unit_price * (1 - i.discount)), 2) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY o.channel
-- answer:
WITH y AS (
  SELECT o.channel,
         SUM(CASE WHEN strftime('%Y', o.order_date) = '2024' THEN i.quantity * i.unit_price * (1 - i.discount) END) AS rev_2024,
         SUM(CASE WHEN strftime('%Y', o.order_date) = '2025' THEN i.quantity * i.unit_price * (1 - i.discount) END) AS rev_2025
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY o.channel
)
SELECT channel, ROUND(rev_2024, 2) AS rev_2024, ROUND(rev_2025, 2) AS rev_2025,
       ROUND(100.0 * (rev_2025 - rev_2024) / rev_2024, 1) AS growth_pct
FROM y

Online grew 77.3% and stores 22.5%: the online shop is where the growth is.

-- exercise: Which month had the biggest month-over-month fall in revenue? Return that one month and its MoM % (1 decimal place).
-- hint: Work out mom_pct in a second CTE, then ORDER BY mom_pct and LIMIT 1. NULLs sort first in SQLite, so filter them out.
WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(i.quantity * i.unit_price * (1 - i.discount)) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY month
)
SELECT month, ROUND(revenue, 2) AS revenue
FROM monthly
-- answer:
WITH monthly AS (
  SELECT strftime('%Y-%m', o.order_date) AS month,
         SUM(i.quantity * i.unit_price * (1 - i.discount)) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY month
),
g AS (
  SELECT month,
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) AS mom_pct
  FROM monthly
)
SELECT month, ROUND(mom_pct, 1) AS mom_pct
FROM g
WHERE mom_pct IS NOT NULL
ORDER BY mom_pct
LIMIT 1

Try it on your own data: export a monthly table and chart it with [[plot-maker]], or run your own CSV through these queries in [[sql-playground]].

  • Build a table with exactly one row per period before working out growth.
  • Growth = (new − old) ÷ old. MoM uses LAG(x) and YoY uses LAG(x, 12) on monthly rows.
  • Write 100.0, not 100, and wrap the divisor in NULLIF(…, 0).
  • LAG counts rows, not months: join a calendar so empty months get a row with 0.
  • Growth multiplies: +10 % then +10 % is +21 %, and +50 % then −50 % is −25 %.

New words: month-over-month (MoM) growth, year-over-year (YoY) growth, seasonality, calendar table.

Check yourself

1Revenue was $4,000 last month and $5,000 this month. What is the month-over-month growth?
2In a table with one row per month, which gives the same month last year?
3Why join a full calendar of months before using LAG?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.