Month-over-Month Growth in SQL: LAG, the Growth Formula, and the Traps

1. The growth formula, and how to say it in a sentence

Before the explanation: revenue was 250 in January and 300 in February. Say the February growth rate out loud before you read the formula.

Month-over-month growth is this month minus last month, divided by last month. In numbers: 300 minus 250 is 50, and 50 divided by 250 is 0.2, which is 20%. The division is the part people skip when talking, and it is the part that makes the number comparable. A jump of 50 is huge for a coffee cart and a rounding error for an airline. Dividing by last month turns the raw change into a share of where you started.

The sentence version matters because you will be asked for it in interviews and in hallways. Practice this exact shape: "Revenue grew 20% month over month, from 250 in January to 300 in February." Rate first, then the two raw numbers. Giving both protects you, because a percentage with no base hides small numbers, and a raw change with no base hides scale.

One vocabulary note. "Month over month" always means this month compared to the month immediately before it. Comparing June to last June is "year over year," and the two answer different questions. Section seven comes back to that.

2. One row per month first, then compare

Before the explanation: the orders table has twelve rows covering five months. How many rows should the table you compare neighbors on have?

Five. Growth is a comparison between months, so before any comparing can happen, the data has to become one row per month. This is a grain move. Grain is what one row means: right now one row is one order, and the question needs one row to be one month. GROUP BY makes that move, and GROUP BY and HAVING covers it in full if the collapse feels magic.

SELECT strftime('%Y-%m', order_date) AS month,
       SUM(amount) AS revenue
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;

The strftime('%Y-%m', ...) part chops a full date like 2025-01-04 down to its month, 2025-01. Other databases spell this differently: DATE_TRUNC('month', order_date) in PostgreSQL, FORMAT(order_date, 'yyyy-MM') in SQL Server. Same idea everywhere. Here is the real output.

Check it by hand once, because trust in the rest of the page flows from this table. January is 120 plus 80 plus 50, which is 250. March is 200 plus 60 plus 100, which is 360. And notice what the table does not have: an April row. Zero orders means zero rows to group, so April is not zero here. It is absent. Keep that in mind.

3. LAG in everyday words

Before the explanation: you have five monthly rows and you need each row to also know the previous row's revenue. Where would that number physically go?

Onto the same row, in a new column. That is the whole job of LAG(). In everyday words: sort the rows, then for each row, reach up to the row above and copy a value down. LAG(revenue) OVER (ORDER BY month) says "sort by month, then hand every row the revenue from the row before it." It is a window function, which means it can see neighboring rows without collapsing them, and window functions covers that family properly.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue
FROM monthly
ORDER BY month;

Once last month sits beside this month, growth is plain arithmetic on one row: revenue minus prev_revenue, divided by prev_revenue. But look closely at that output first. Two of the five rows already contain this page's remaining traps. January's prev_revenue is NULL. And May's prev_revenue is 360, which is March's number. Both get their own section.

The ORDER BY month inside the parentheses is not decoration. LAG has no idea what a month is. It only knows "the row before this one in the order I was given." Sort by the wrong column and LAG will cheerfully hand you the revenue of some unrelated row.

4. Two quiet traps: integer division and the NULL first month

Before the explanation: 50 divided by 250 is 0.2. What will a database answer if both numbers are stored as whole numbers?

Zero. In most databases, dividing one integer by another throws away the remainder, because an integer column promises whole numbers. So (revenue - prev_revenue) / prev_revenue is 50 / 250, which integer division rounds down to 0. Every growth rate between minus 99% and plus 99% becomes zero, the query runs without complaint, and the report says growth is flat. I ran exactly that against this table and every single month came back 0.

The fix is to force decimal math before the division happens. Multiplying by 100.0 first does it, because one decimal number in the expression makes the whole expression decimal:

ROUND(100.0 * (revenue - prev_revenue) / prev_revenue, 1) AS growth_pct

That returns 20.0 for February instead of 0. CAST(revenue AS REAL) does the same job if you prefer it spelled out.

The second quiet trap is January. Its prev_revenue is NULL, because LAG reached up from the first row and found nothing there. NULL is SQL's marker for unknown, a hole where a value would go, and NULL in SQL is a whole guide on how those holes behave. Here the behavior is actually merciful: arithmetic with NULL yields NULL, so January's growth prints as NULL rather than as a fake number. Leave it that way. A NULL first month is the honest answer, because growth from before your data started is genuinely unknown. The only mistake is "fixing" it to zero, which claims flat growth you have no evidence for.

5. The missing-month trap, shown actually happening

Here is the full naive growth query. It looks finished. Say what May's growth_pct will be, and against which month, before you look at the output.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

The May row reports a 30.6% drop. That number is labeled month over month, and it is not month over month. April had no orders, so April has no row, so the row above May is March. LAG did its job perfectly: it fetched the previous row. The previous row was two calendar months ago. Nothing errors, nothing warns, and the report now says one month's decline when the truth is a two-month slide with a dead month in the middle.

Say out loud why May compared itself to March before reading the fix. If you can state it, you can spot it in any query for the rest of your career.

The fix is to build the calendar yourself and attach the data to it, so every month has a row whether or not it had orders. In SQLite a recursive CTE generates the months. PostgreSQL has generate_series for the same job, and in a warehouse you usually join to a date dimension table that already exists.

WITH RECURSIVE calendar(month) AS (
  SELECT '2025-01'
  UNION ALL
  SELECT strftime('%Y-%m', date(month || '-01', '+1 month'))
  FROM calendar
  WHERE month < '2025-06'
),
monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT c.month,
       COALESCE(m.revenue, 0) AS revenue,
       LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month) AS prev_revenue,
       ROUND(
         100.0 * (COALESCE(m.revenue, 0)
                  - LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month))
         / NULLIF(LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month), 0),
       1) AS growth_pct
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

Three small parts carry the weight. The LEFT JOIN keeps every calendar month even when no orders match it. COALESCE(m.revenue, 0) turns the empty April into an actual zero, which is correct here because zero orders really did mean zero revenue. And NULLIF(..., 0) guards the division: growth from a zero month would divide by zero, so it returns NULL instead. Here is the real output.

Now the story is true. April fell 100%, to nothing. May's growth is NULL because a percentage climb from zero is undefined, and NULL is the honest way to print undefined. The fake single-month 30.6% drop is gone, replaced by what actually happened: a collapse and a restart.

6. The full before and after

Same table, same question: how is revenue growing month over month?

Before

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly;

Three defects, zero error messages. Integer division makes every growth rate print as 0. The missing April makes May compare against March. And nothing on the screen tells the reader either thing happened.

After

-- ============================================================
-- Month-over-month revenue growth.
-- WHY the calendar CTE: LAG compares adjacent ROWS. April has
--      no orders, so without a calendar row May would compare
--      against March and report a fake one-month drop.
-- WHY 100.0: integer division would round every rate to 0.
-- WHY NULLIF: growth from a zero month is undefined, and NULL
--      is the honest way to report undefined.
-- ============================================================
WITH RECURSIVE calendar(month) AS (
  SELECT '2025-01'
  UNION ALL
  SELECT strftime('%Y-%m', date(month || '-01', '+1 month'))
  FROM calendar
  WHERE month < '2025-06'
),
monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT c.month,
       COALESCE(m.revenue, 0) AS revenue,
       ROUND(
         100.0 * (COALESCE(m.revenue, 0)
                  - LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month))
         / NULLIF(LAG(COALESCE(m.revenue, 0)) OVER (ORDER BY c.month), 0),
       1) AS growth_pct
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

Every guard is present and every guard states its reason in a comment, in the style from how to comment SQL so it teaches. A reviewer can now disagree with a choice instead of discovering it.

Picture running the after version on your own revenue or signups table right now. Which month would come back with a gap row you did not know about? If you cannot answer, that is precisely the reason to run it. And the pattern only becomes yours once your fingers have produced it, so type the query yourself rather than pasting it.

7. Edge cases: reading negatives, formatting, MoM against YoY

Before the explanation: revenue fell 30% one month, then rose 30% the next. Is it back where it started?

No, and that is the negative-growth reading trap. Down 30% from 100 is 70. Up 30% from 70 is 91. Percent changes compound from wherever they land, so a drop needs a larger rise to undo it. When you present a fall, say the level too: "down 30.6%, from 360 to 250." The level pair keeps readers from mentally reversing the percentage.

Format at the last moment. Keep growth as a plain number, like -30.6, all the way through the SQL, and let the report layer add the percent sign. Strings like '-30.6%' cannot be sorted, averaged, or charted, and someone always tries. ROUND(x, 1) is enough precision for a business audience; 20.03847% communicates nothing that 20.0% does not.

A tiny base makes a silly percentage. Growth from 2 to 10 is 400%. True, and useless. When last month is near zero, report the raw change alongside the rate or suppress the rate below a floor you write down.

MoM is noisy where the calendar is seasonal. Ice cream sales fall every September, and no one should get an alarmed email about it. Month over month asks "what just changed," so it amplifies seasonality and one-off spikes. Year over year compares June to last June, so the season cancels out and the trend shows through. Use MoM to detect turns quickly, YoY to state the trend calmly, and both lines on the same chart when the audience can take it. The Forecasting Kit works through seasonality properly, including when a "drop" is just the calendar breathing.