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

By Michael Nocito, data analyst · Published August 7, 2026 By the end of this page you can write a month-over-month growth query and trust its answer. You will know the growth formula and how to say it in a sentence, what LAG() actually does, and the three traps that produce wrong percentages without producing an error: integer division, the empty first month, and the missing month. It is about twenty minutes. Here is what to actually do today. Before you trust any growth query you have already written, run one check: count the distinct months in your data and compare that count to the calendar. If any month is missing, at least one of your growth numbers is comparing the wrong pair of months. The short version: growth is this month minus last month, divided by last month. LAG() fetches last month's value onto this month's row. But LAG() fetches the previous row, not the previous month, and those are only the same thing when no month is missing. That last distinction is the one that bites, so it gets the picture. The original carries a diagram here. In words: Two side-by-side panels, each a vertical column of month rows. In the left panel the rows are January, February, March, and then May, because April is missing from the data. Curved arrows run from each row up to the row above it: February points to January, March points to February, and May points to March. The May-to-March arrow is drawn in a warning color with a cross beside it, because May is being compared to March, skipping the missing April. In the right panel the same months appear but a dashed April row has been inserted between March and May. Now the arrow from May points to April, and every arrow connects true calendar neighbors. A check mark sits beside the May-to-April arrow. The picture shows that LAG connects adjacent rows, so growth is only month-over-month when every calendar month has a row. The worked example is real. Every query on this page ran in SQLite against the twelve-row orders table shown below, and every output is pasted from the run. SQLite has had window functions since version 3.25, so you can reproduce all of it on your own machine today. If grouping itself is new, read GROUP BY and HAVING first and come back. Here is the whole dataset. Twelve orders, five months, and no orders at all in April. That empty April is on purpose, because it is the star of trap number three. | order_id | order_date | amount | |---|---|---| | 1 | 2025-01-04 | 120 | | 2 | 2025-01-18 | 80 | | 3 | 2025-01-27 | 50 | | 4 | 2025-02-03 | 90 | | 5 | 2025-02-14 | 130 | | 6 | 2025-02-25 | 80 | | 7 | 2025-03-08 | 200 | | 8 | 2025-03-19 | 60 | | 9 | 2025-03-30 | 100 | | 10 | 2025-05-06 | 110 | | 11 | 2025-05-21 | 140 | | 12 | 2025-06-11 | 250 | 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. | month | revenue | |---|---| | 2025-01 | 250 | | 2025-02 | 300 | | 2025-03 | 360 | | 2025-05 | 250 | | 2025-06 | 250 | 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; | month | revenue | prev_revenue | |---|---|---| | 2025-01 | 250 | NULL | | 2025-02 | 300 | 250 | | 2025-03 | 360 | 300 | | 2025-05 | 250 | 360 | | 2025-06 | 250 | 250 | 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; | month | revenue | prev_revenue | growth_pct | |---|---|---|---| | 2025-01 | 250 | NULL | NULL | | 2025-02 | 300 | 250 | 20.0 | | 2025-03 | 360 | 300 | 20.0 | | 2025-05 | 250 | 360 | -30.6 | | 2025-06 | 250 | 250 | 0.0 | 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 h