SQL LAG Function: Month-Over-Month Change
· 7 min read
LAG is a window function that returns a value from an earlier row, so you can compare each row with the one before it. The classic use of SQL LAG is month-over-month change: put last month's number next to this month's, then subtract.
SELECT month, signups,
LAG(signups) OVER (ORDER BY month) AS prev_signups
FROM plan_signups
WHERE plan = 'Basic'
ORDER BY month;The ORDER BY inside OVER decides what "previous" means. Here it is the previous month. This guide shows the output, percent change, PARTITION BY for several groups, LEAD, the optional arguments, and the gap in the data that makes LAG compare the wrong months. For a broader introduction to OVER, see SQL window functions explained.
The example table
| plan | month | signups |
|---|---|---|
| Basic | 2025-01-01 | 120 |
| Basic | 2025-02-01 | 150 |
| Basic | 2025-03-01 | 135 |
| Basic | 2025-04-01 | 180 |
| Pro | 2025-01-01 | 40 |
| Pro | 2025-02-01 | 44 |
| Pro | 2025-04-01 | 55 |
New signups per plan per month, with the month stored as its first day. Pro has no row for March. Keep that in mind.
LAG returns the previous row's value
The query at the top returns:
| month | signups | prev_signups |
|---|---|---|
| 2025-01-01 | 120 | NULL |
| 2025-02-01 | 150 | 120 |
| 2025-03-01 | 135 | 150 |
| 2025-04-01 | 180 | 135 |
Each row now carries the value from the row before it. January has nothing before it, so LAG returns NULL, which is correct: there is no month to compare with. LAG adds a column and removes no rows. The WHERE runs first, so LAG only sees the Basic rows.
Month-over-month change and percent change
With the previous value on the same row, change is a subtraction and percent change is a division:
SELECT month, signups,
signups - LAG(signups) OVER (ORDER BY month) AS change,
ROUND(100.0 * (signups - LAG(signups) OVER (ORDER BY month))
/ LAG(signups) OVER (ORDER BY month), 1) AS pct_change
FROM plan_signups
WHERE plan = 'Basic'
ORDER BY month;| month | signups | change | pct_change |
|---|---|---|---|
| 2025-01-01 | 120 | NULL | NULL |
| 2025-02-01 | 150 | 30 | 25 |
| 2025-03-01 | 135 | -15 | -10 |
| 2025-04-01 | 180 | 45 | 33.3 |
Three details matter:
- Divide by the previous value, not the current one. February grew 30 on a base of 120, which is 25%.
- Use
100.0, not100. In Postgres and SQL Server, an integer divided by an integer drops the decimals, so 45 / 135 would come out as 0. DuckDB divides as decimals either way, but the habit keeps your query portable. - Guard against zero. If the previous month could be 0, divide by
NULLIF(LAG(...) OVER (...), 0)so the result isNULLinstead of an error.
To avoid repeating LAG(...) OVER (...), name the window once (WINDOW w AS (ORDER BY month), then OVER w, in DuckDB, Postgres and MySQL 8) or compute prev_signups in a CTE. The Excel version is in the percent change formula guide.
LAG with PARTITION BY: one comparison per group
Remove the WHERE and you have two plans in one table. PARTITION BY plan makes LAG restart for each plan:
SELECT plan, month, signups,
LAG(signups) OVER (PARTITION BY plan ORDER BY month) AS prev_signups
FROM plan_signups
ORDER BY plan, month;| plan | month | signups | prev_signups |
|---|---|---|---|
| Basic | 2025-01-01 | 120 | NULL |
| Basic | 2025-02-01 | 150 | 120 |
| Basic | 2025-03-01 | 135 | 150 |
| Basic | 2025-04-01 | 180 | 135 |
| Pro | 2025-01-01 | 40 | NULL |
| Pro | 2025-02-01 | 44 | 40 |
| Pro | 2025-04-01 | 55 | 44 |
Every plan's first month has a NULL. That is how you check a partitioned LAG at a glance: one NULL per group.
Without PARTITION BY, values leak between groups
Write OVER (ORDER BY plan, month) instead, with the plan only in the ORDER BY, and every row is the same except one: Pro's January gets 180, Basic's April, and reports a fall of 140 that never happened. Six of seven rows look right, so it survives a quick look. It is one of the most common mistakes in AI-written window queries, and the window functions guide shows it in full. Only one NULL in the whole result means the PARTITION BY is missing.
LAG offset, default value, and LEAD
LAG takes up to three arguments: LAG(value, offset, default). LEAD takes the same ones and looks forward instead of back.
SELECT month, signups,
LAG(signups, 1, 0) OVER (ORDER BY month) AS prev_or_zero,
LAG(signups, 2) OVER (ORDER BY month) AS two_back,
LEAD(signups) OVER (ORDER BY month) AS next_signups
FROM plan_signups
WHERE plan = 'Basic'
ORDER BY month;| month | signups | prev_or_zero | two_back | next_signups |
|---|---|---|---|---|
| 2025-01-01 | 120 | 0 | NULL | 150 |
| 2025-02-01 | 150 | 120 | NULL | 135 |
| 2025-03-01 | 135 | 150 | 120 | 180 |
| 2025-04-01 | 180 | 135 | 150 | NULL |
- The offset is how many rows back.
LAG(signups, 12)on monthly data gives the same month last year, if no months are missing. - The default replaces the
NULLwhen there is no earlier row. Careful: a default of 0 turns January's change into +120. For change calculations, theNULLis usually the honest answer. LEADreturns the next row, handy for "time until the next event".
The missing-month trap
LAG goes back one row, not one month. Look at Pro in the partitioned result again: April's previous value is 44, which is February. There is no March row, so LAG skipped straight past it. Any "month-over-month" change for Pro in April is really a two-month change.
You can detect gaps by lagging the date itself:
SELECT plan, month, signups,
date_diff('month', LAG(month) OVER (PARTITION BY plan ORDER BY month), month) AS months_since_prev
FROM plan_signups
ORDER BY plan, month;| plan | month | signups | months_since_prev |
|---|---|---|---|
| Basic | 2025-01-01 | 120 | NULL |
| Basic | 2025-02-01 | 150 | 1 |
| Basic | 2025-03-01 | 135 | 1 |
| Basic | 2025-04-01 | 180 | 1 |
| Pro | 2025-01-01 | 40 | NULL |
| Pro | 2025-02-01 | 44 | 1 |
| Pro | 2025-04-01 | 55 | 2 |
date_diff is DuckDB. In Postgres, subtracting two dates gives days, and month gaps take EXTRACT on an age() interval.
The fix is to build a complete grid of every plan and month first, then run LAG on that:
WITH months AS (
SELECT CAST(m AS DATE) AS month
FROM generate_series(DATE '2025-01-01', DATE '2025-04-01', INTERVAL 1 MONTH) AS t(m)
),
grid AS (
SELECT p.plan, m.month, COALESCE(s.signups, 0) AS signups
FROM (SELECT DISTINCT plan FROM plan_signups) AS p
CROSS JOIN months AS m
LEFT JOIN plan_signups AS s
ON s.plan = p.plan AND s.month = m.month
)
SELECT plan, month, signups,
LAG(signups) OVER (PARTITION BY plan ORDER BY month) AS prev_signups
FROM grid
WHERE plan = 'Pro'
ORDER BY month;| plan | month | signups | prev_signups |
|---|---|---|---|
| Pro | 2025-01-01 | 40 | NULL |
| Pro | 2025-02-01 | 44 | 40 |
| Pro | 2025-03-01 | 0 | 44 |
| Pro | 2025-04-01 | 55 | 0 |
Now April is compared with March. Whether March really is 0 is a question about the data: a missing row might mean "nobody signed up" or "March never loaded". Check before you fill. generate_series works in DuckDB and Postgres (where it returns timestamps, hence the cast), and the LEFT JOIN keeps the empty month.
Filtering on LAG needs a CTE
You cannot put LAG in WHERE. DuckDB says WHERE clause cannot contain window functions!, and Postgres gives a similar error, because window functions run after WHERE. Compute it first, then filter:
WITH changes AS (
SELECT plan, month, signups,
LAG(signups) OVER (PARTITION BY plan ORDER BY month) AS prev_signups
FROM plan_signups
)
SELECT plan, month, signups, prev_signups
FROM changes
WHERE signups < prev_signups;| plan | month | signups | prev_signups |
|---|---|---|---|
| Basic | 2025-03-01 | 135 | 150 |
Only Basic's March fell. First rows drop out on their own, since a comparison with NULL is never true. See SQL order of execution.
FAQ
What is the difference between LAG and LEAD in SQL?
LAG reads from an earlier row and LEAD from a later row, in the order set by the ORDER BY inside OVER. LAG(x) OVER (ORDER BY month DESC) behaves like LEAD(x) OVER (ORDER BY month).
Why does LAG return NULL?
The first row of each partition has no earlier row. Pass a third argument to replace it, or wrap it in COALESCE, but only when a substitute value makes sense.
Which databases support LAG?
DuckDB, Postgres, MySQL 8 and later, SQL Server 2012 and later, Oracle, SQLite 3.25 and later, BigQuery and Snowflake.
You can practise LAG on a full year of one company's orders on the practice page, including one where you check an AI assistant's query.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- This month next to last month
- Month-over-month change in percent
- Previous month for East and WestAudit
- Longest gap between orders, Enterprise