SQL LAG and LEAD Explained: Compare Each Row With Its Neighbours
SQL LAG reads a value from the previous row and LEAD reads from the next row, with no self join. Learn the syntax, partitions, offsets and defaults through five tested examples: change, percent growth, next values, two-back comparisons and time gaps.
- SQL LAG returns a value from an earlier row and LEAD from a later row, in the order you define with
OVER (ORDER BY ...). PARTITION BYrestarts the comparison for each group, so one region never borrows another region’s numbers.- Edge rows get NULL unless you pass a default as the third argument.
- You cannot use LAG in
WHERE. Compute it in a subquery or CTE, then filter outside.
“How much did revenue change from last month?” “How long since this user last logged in?” “Which day did the price drop?” All of these compare a row with its neighbour. Before window functions, you answered them with a self join on a calculated row number, which was slow to write and easy to get wrong. SQL LAG and its twin LEAD do it in one expression.
Every query below ran on SQLite 3.50.4. The same syntax works in PostgreSQL, MySQL 8+, SQL Server 2012+, Oracle, and warehouses such as Snowflake and BigQuery.
What do LAG and LEAD do?
Both are window functions. They look at other rows in a defined order without collapsing the result, so every input row still appears in the output. LAG looks backwards, LEAD looks forwards. If window functions are new to you, start with our SQL window functions guide.
Syntax
LAG(expression [, offset [, default]]) OVER (
[PARTITION BY group_columns]
ORDER BY sort_columns
)
- expression: the column or calculation to fetch.
- offset: how many rows back (or forward for LEAD). Defaults to 1.
- default: the value to return when that row does not exist. Defaults to NULL.
- PARTITION BY: optional groups that each get their own sequence.
- ORDER BY: required in practice. Without it, “previous” has no meaning.
Sample data
CREATE TABLE sales(region TEXT, month TEXT, revenue INT);
INSERT INTO sales VALUES
('North','2026-07',120), ('North','2026-08',150), ('North','2026-09',135),
('North','2026-10',180), ('South','2026-07',90), ('South','2026-08',90),
('South','2026-09',110);
Example 1: previous value and change
We define the window once with a named WINDOW clause, then reuse it. This keeps the query short and guarantees every function uses the same order:
SELECT region, month, revenue,
LAG(revenue) OVER w AS prev,
revenue - LAG(revenue) OVER w AS change
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);
The output appears in the combined table under example 2. The first row of each region has no previous month, so prev and change are NULL there. That is correct: there is nothing to compare with. The named WINDOW clause works in PostgreSQL, MySQL 8, SQLite and SQL Server 2022. In older versions, repeat the full OVER (...) each time.
Example 2: percent growth
Percent change is the most common reporting use. Multiply by 100.0 to force decimal division:
SELECT region, month, revenue,
LAG(revenue) OVER w AS prev,
revenue - LAG(revenue) OVER w AS change,
ROUND(100.0 * (revenue - LAG(revenue) OVER w) / LAG(revenue) OVER w, 1) AS pct,
LEAD(month) OVER w AS next_month
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);
| region | month | revenue | prev | change | pct | next_month |
|---|---|---|---|---|---|---|
| North | 2026-07 | 120 | NULL | NULL | NULL | 2026-08 |
| North | 2026-08 | 150 | 120 | 30 | 25.0 | 2026-09 |
| North | 2026-09 | 135 | 150 | -15 | -10.0 | 2026-10 |
| North | 2026-10 | 180 | 135 | 45 | 33.3 | NULL |
| South | 2026-07 | 90 | NULL | NULL | NULL | 2026-08 |
| South | 2026-08 | 90 | 90 | 0 | 0.0 | 2026-09 |
| South | 2026-09 | 110 | 90 | 20 | 22.2 | NULL |
Notice that South July shows NULL, not North October’s 180. PARTITION BY region stopped values leaking across regions. If the previous value can be zero, guard the division with NULLIF(LAG(revenue) OVER w, 0) so you get NULL instead of an error.
Example 3: the next value with LEAD
The next_month column above comes from LEAD(month). It is NULL on the last row of each region. LEAD is handy for “valid until” ranges: if each row is a price that started on a date, LEAD(start_date) gives the date the next price took over, which is when this one ended. It also powers “what happened next” analysis, such as the step a user took after viewing a page.
Example 4: offset and default value
SELECT month, revenue,
LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev0,
LAG(revenue, 2) OVER (ORDER BY month) AS two_back
FROM sales WHERE region = 'North';
| month | revenue | prev0 | two_back |
|---|---|---|---|
| 2026-07 | 120 | 0 | NULL |
| 2026-08 | 150 | 120 | NULL |
| 2026-09 | 135 | 150 | 120 |
| 2026-10 | 180 | 135 | 150 |
The default 0 replaced the NULL in the first row. Be careful with defaults, though. A default of 0 makes the first month look like growth from nothing, which can distort averages. NULL is often the more honest answer. An offset of 2 compares with two rows back, useful for “same quarter last year” style comparisons when each row is a quarter: use an offset of 4.
Example 5: time gaps between events
LAG works on any type, including dates. Here we measure minutes between logins for each user:
SELECT user, ts,
ROUND((julianday(ts) - julianday(
LAG(ts) OVER (PARTITION BY user ORDER BY ts))) * 1440) AS gap_min
FROM logins;
| user | ts | gap_min |
|---|---|---|
| u1 | 2026-11-01 09:00 | NULL |
| u1 | 2026-11-01 09:20 | 20.0 |
| u1 | 2026-11-01 11:05 | 105.0 |
| u2 | 2026-11-01 10:00 | NULL |
| u2 | 2026-11-01 10:10 | 10.0 |
This is the basis of sessionisation: if the gap is over 30 minutes, start a new session. Date arithmetic differs by database. PostgreSQL subtracts timestamps directly, and SQL Server uses DATEDIFF(minute, LAG(ts) OVER (...), ts). See our SQL date functions guide for each dialect.
Filtering on LAG results
Suppose you want only the months where revenue fell. The obvious query fails:
SELECT revenue FROM sales WHERE LAG(revenue) OVER (ORDER BY month) > 1;
-- OperationalError: misuse of window function LAG()
Window functions run after WHERE, so they cannot appear in it. Wrap the query and filter outside:
SELECT * FROM (
SELECT region, month, revenue,
LAG(revenue) OVER (PARTITION BY region ORDER BY month) AS prev
FROM sales
) WHERE revenue < prev;
-- North | 2026-09 | 135 | 150
Only North September dropped. A CTE works the same way and reads better in longer queries.
LAG vs a self join
The old approach numbered rows and joined the table to itself on a.rn = b.rn + 1. It still works, but it is longer, reads the table twice, and breaks easily when you add partitions. LAG states the intent directly, and databases can compute it in a single sorted pass. Keep the self join for genuine row-to-row relationships, such as employees and their managers.
Testing tip: when you test reports built on LAG, include a group with only one row, a group with a repeated value, and a previous value of zero. Those three cases catch most edge bugs.
Common mistakes
- Missing PARTITION BY, so groups borrow values from each other.
- Ordering by the wrong column, or by a column with ties. Add a tiebreaker such as an ID for a deterministic result.
- Using LAG in WHERE. Use a subquery or CTE.
- Assuming LAG skips gaps. It returns the previous row, not the previous calendar month. If a month is missing, the comparison spans two months. Fill gaps first with a calendar table.
- Integer division in percent calculations. Multiply by 100.0, not 100.
- Careless defaults that create fake growth.
Related functions: FIRST_VALUE and LAST_VALUE
LAG compares with the row next door. Sometimes you want to compare with the start of the period instead, for example growth since the first month. FIRST_VALUE does that:
SELECT region, month, revenue,
revenue - FIRST_VALUE(revenue) OVER w AS since_first,
LAST_VALUE(revenue) OVER w AS last_default,
LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY month);
For North, since_first came out as 0, 30, 15 and 60, all measured against July’s 120. The surprise is LAST_VALUE. With the default frame, last_default simply repeated each row’s own revenue (120, 150, 135, 180), because the default window frame stops at the current row. Only last_full, with the frame widened to UNBOUNDED FOLLOWING, returned the true last value, 180 for North and 110 for South. LAG and LEAD ignore the frame, so they do not have this trap. Our running total guide explains window frames in more detail.
Final thoughts
SQL LAG and LEAD turn “compare with the previous row” into one readable expression. Define the order, partition by your groups, decide what edge rows should show, and filter in an outer query. With the five patterns above, you can build month over month reports, price histories and session analysis without a single self join.
FAQ
What does LAG do in SQL?
LAG is a window function that returns a value from a previous row in the order you define, by default the row directly before, without a self join.
What is the difference between LAG and LEAD?
LAG looks at earlier rows and LEAD looks at later rows. Both take the same arguments: an expression, an optional offset and an optional default.
Why does LAG return NULL on the first row?
There is no earlier row to read, so LAG returns NULL. Pass a third argument to use a default value instead.
Can I use LAG in a WHERE clause?
No. Window functions run after WHERE. Compute LAG in a subquery or CTE, then filter in the outer query.
Which databases support LAG and LEAD?
PostgreSQL, MySQL 8 and later, SQL Server 2012 and later, Oracle, SQLite 3.25 and later, and cloud warehouses such as Snowflake and BigQuery.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.