LAG and LEAD for Previous and Next Values

1. The problem: accessing neighboring rows without a self-join

The name LAG stands in for the entire class of these problems: whenever a calculation needs the context of a neighboring row, an offset function is usually the most direct and maintainable solution, long before a self-join or a procedural loop should even be considered.

Many analyses need not only the value of the current row, but also the value of the immediately preceding or following row within a sorted order. Classic examples are comparing the current month's revenue to the prior month, computing the difference between two consecutive sensor readings, or detecting gaps in a sequential numbering. Before LAG and LEAD existed as window functions, every one of these problems had to be solved with a self-join, joining a table with itself, where the join condition reproduces the desired row shift.

A self-join for this purpose is error prone, hard to read, and often slow on larger tables, because it effectively doubles the table and has to evaluate an additional join condition that is not always efficient even with a matching index. LAG and LEAD solve this problem elegantly directly within a single SELECT clause, without a join, without a subquery, and without needing to reference the table a second time.

Both functions belong to the group of so-called offset functions within window functions, which, besides LAG and LEAD, also includes FIRST_VALUE, LAST_VALUE and NTH_VALUE. All offset functions share the property of referring to a different row within the same window, instead of just the current row or an aggregation over multiple rows.

2. LAG: accessing the previous row

It is important that LAG strictly refers to the logical order defined by ORDER BY, not the physical storage order of the rows in the table. Two identical queries with a different ORDER BY inside the OVER clause therefore return completely different results for the same LAG computation, because the definition of "previous row" changes accordingly.

LAG(column) OVER (ORDER BY sort_column) returns, for every row, the value of the column from the row that immediately precedes the current row in the defined order. For the very first row within the partition, there naturally is no previous row, which is why LAG returns NULL there by default, unless an explicit default value was specified. The name LAG describes accurately that the returned value "lags behind" the current row.

The practical value of LAG lies in computing changes between consecutive rows without duplicating the table. A typical pattern is revenue - LAG(revenue) OVER (ORDER BY month), which computes the absolute change in revenue compared to the prior month directly within the same query. This kind of delta calculation is one of the most common applications of LAG in reporting and analytics queries.

-- LAG: access the value from the previous row, no self-join needed
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue
ORDER BY month;

-- Result
-- month      | revenue | prev_month_revenue | revenue_change
-- 2026-04    |   45000 |                NULL |            NULL
-- 2026-05    |   48000 |               45000 |           3000
-- 2026-06    |   46500 |               48000 |          -1500
-- 2026-07    |   51000 |               46500 |           4500

3. LEAD: accessing the next row

A common misunderstanding about LEAD is assuming it can predict future events that have not yet occurred. In reality, LEAD only accesses rows already present in the result set that simply come after the current row in the defined sort order, not actually future, not-yet-existing data.

LEAD(column) OVER (ORDER BY sort_column) is the direct counterpart to LAG and returns, for every row, the value of the column from the row that immediately follows the current row in the defined order. For the very last row within the partition, there naturally is no next row, which is why LEAD also returns NULL there by default. The name LEAD describes that the returned value "leads ahead" of the current row.

LEAD is typically used to compute forward-looking comparisons, for instance how long a customer takes until their next order, or whether an event is followed by another one within a certain timeframe. A common pattern is computing the time difference between two consecutive events with LEAD(timestamp) OVER (...) - timestamp, which for example gives the time between two sessions or the time until a device's next maintenance.

-- LEAD: access the value from the next row
SELECT
    session_id,
    started_at,
    LEAD(started_at) OVER (ORDER BY started_at) AS next_session_start,
    LEAD(started_at) OVER (ORDER BY started_at) - started_at AS gap_to_next
FROM user_sessions
ORDER BY started_at;

4. Offset parameter: more than one row back or forward

A negative offset value is not allowed in most database systems and results in a syntax error. Anyone who instead needs the opposite direction simply uses LEAD instead of LAG with the same positive offset, rather than trying to reverse the direction through a negative sign.

Both LAG and LEAD accept an optional second parameter, the offset, which specifies how many rows back or forward the function should reach. The default value is 1, which corresponds to the immediately previous or next row. With LAG(revenue, 12) OVER (ORDER BY month), you can, for instance, retrieve the revenue from exactly twelve months ago, enabling a year-over-year comparison within a single column, without a second join or a second query.

This offset parameter makes LAG and LEAD considerably more flexible than a plain self-join with a fixed shift, because the offset can be shaped as a variable or parameter of the query. An offset of 3 with LEAD returns the value three rows further ahead, an offset of 2 with LAG the value two rows back. This parameterizability is especially valuable for analyses that want to compare several time horizons at once, such as prior month, prior quarter and prior year within the same query.

-- Offset parameter: compare to 12 months ago (year-over-year)
SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS revenue_year_ago,
    ROUND(
        100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0),
        2
    ) AS yoy_growth_pct
FROM monthly_revenue
ORDER BY month;

5. Default values for edge rows

The default value parameter can also be given as an expression, not just as a constant. LAG(amount, 1, amount) OVER (...), for instance, returns the row's own amount for the first row, which automatically sets a difference calculation for the first row to zero, without needing a separate CASE expression for this edge case.

Both LAG and LEAD accept an optional third parameter, which serves as the return value when no previous or next row exists, instead of the default behavior of returning NULL. LAG(revenue, 1, 0) OVER (ORDER BY month) returns 0 instead of NULL for the very first row, which is often more practical in subsequent calculations such as differences or percentages, because NULL values propagate through arithmetic expressions and can turn the entire result into NULL.

The choice between NULL and an explicit default value is a business decision, not a purely technical one. NULL correctly signals that no comparison basis simply exists for this row, which is often the better default for correct data representation. A default value like 0, on the other hand, can be misleading from a business perspective if it suggests the prior month actually had zero revenue, instead of simply not existing. This decision should be made deliberately, not out of convenience.

-- Default value instead of NULL for the first row
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue_or_zero,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue
ORDER BY month;

6. Month-over-month comparisons with LAG

In dashboards, the percentage change is often additionally color coded, for instance green for an increase and red for a decrease. This visual treatment typically happens in the application layer, but the underlying calculation with LAG stays entirely in the database, which ensures consistency of the metric across different frontends.

The month-over-month comparison, comparing a metric to the direct prior month, is one of the most common applications of LAG in business reporting systems overall. Besides the plain absolute difference, the percentage change is usually the more relevant metric, because it stays comparable independent of the absolute magnitude. The formula for that is (current_value - previous_value) / previous_value, where NULLIF should be used to avoid a division by zero if the prior value was zero.

An often overlooked aspect of month-over-month calculations is correctly handling gaps in the data. If no row exists for a given month at all, for instance because no revenue was recorded that month, LAG returns the value of the closest existing prior month, not necessarily the immediately preceding calendar month. Anyone who wants to guarantee gapless calendar month comparisons has to pre-fill the source data with a generated calendar table before applying LAG, since LAG itself has no awareness of calendar gaps.

7. LAG and LEAD combined with PARTITION BY

If PARTITION BY is missing in such a situation, incorrect comparisons arise at the transitions between two groups, because the last row of one group gets incorrectly compared to the first row of the next group.

Like most window functions, LAG and LEAD reach their full practical potential only in combination with PARTITION BY, which restricts the before-after comparison to individual groups. Without PARTITION BY, LAG in a query with multiple regions would return the prior month value of the chronologically preceding row, regardless of whether that row belongs to the same region. With PARTITION BY region ORDER BY month, the comparison stays clean within each region, and the first row of every region again returns NULL or the defined default value, because PARTITION BY strictly separates the groups from each other.

This pattern of PARTITION BY plus LAG or LEAD is the standard solution for every before-after comparison that needs to be computed separately per customer, per product, or per region. It fully replaces the previously necessary combination of a self-join with an additional condition on group membership, which, on top of the temporal shift, also had to ensure that both joined rows belonged to the same group.

An often overlooked advantage of PARTITION BY in combination with LAG and LEAD is the ability to compute several independent before-after comparisons in the same query, without the groups affecting each other. A single query can simultaneously include LAG(revenue) OVER (PARTITION BY region ORDER BY month) and LAG(order_count) OVER (PARTITION BY region ORDER BY month), where both functions use the same partitioning but reference different columns. This flexibility makes it possible to represent multidimensional before-after analyses in a single, well maintainable query.

8. Self-join vs. LAG/LEAD: performance and readability

The performance difference between a self-join and LAG or LEAD is substantial on larger tables. A self-join for a before-after comparison effectively joins the table with itself, which multiplies the number of row combinations to process, especially if the join condition is not perfectly selective or no optimal index exists. LAG and LEAD, on the other hand, sort the data once and read it in a single pass, with the database internally just carrying a pointer to the previous or next row.

Beyond raw performance, readability is an underrated advantage of LAG and LEAD. A self-join with a shift condition like ON a.rank = b.rank + 1 is not immediately understandable to other developers and often requires comments to explain the intent. LAG(column) OVER (ORDER BY sort_column), in contrast, communicates its intent directly in the function name and is intuitively understandable even for SQL beginners after a brief explanation. This readability advantage reduces maintenance effort and error rates for future changes to the query.

9. Advanced scenarios: trend analysis with multiple offsets

Such advanced trend classifications can additionally be combined with COUNT() OVER, for instance to count how many consecutive periods a trend has already lasted, which is useful for alerting logic in monitoring systems and automated reporting commentary.

LAG and LEAD can be combined multiple times within the same query using different offset values to build more complex trend analyses. A single query can simultaneously retrieve the prior month with offset 1, the prior quarter with offset 3, and the prior year with offset 12, all within the same SELECT without additional joins. This technique works excellently for dashboards that want to juxtapose several time horizons at once, for instance in financial reporting with monthly, quarterly and yearly comparisons in the same row.

Another advanced application is combining LAG with a conditional expression to classify trend direction: CASE WHEN revenue > LAG(revenue) OVER (...) THEN 'rising' WHEN revenue < LAG(revenue) OVER (...) THEN 'falling' ELSE 'stable' END produces a readable text classification of the development directly in the query. Detecting gaps in sequential sequences, for instance missing invoice numbers, can also be elegantly solved with LEAD(number) OVER (ORDER BY number) - number > 1 as a condition, without needing an external reference table of expected values.

One last aspect worth mentioning is combining LAG or LEAD with FIRST_VALUE and LAST_VALUE, the two other offset functions within a window function. While LAG and LEAD operate relative to the current row, FIRST_VALUE and LAST_VALUE return the value of the first or last row within the defined window, independent of the current row's position. Combined, they let you build analyses that juxtapose both the immediate prior value and the starting value of an entire time series in the same query, for instance to show the overall development since the start of the measurement series alongside the change from the prior month.

-- Trend classification and gap detection in one query
SELECT
    invoice_number,
    invoice_date,
    CASE
        WHEN amount > LAG(amount) OVER (ORDER BY invoice_date) THEN 'rising'
        WHEN amount < LAG(amount) OVER (ORDER BY invoice_date) THEN 'falling'
        ELSE 'stable'
    END AS trend,
    LEAD(invoice_number) OVER (ORDER BY invoice_number) - invoice_number > 1 AS has_gap_after
FROM invoices
ORDER BY invoice_date;

10. Summary

LAG and LEAD solve access to previous and next row values directly within a window function, without the detour through a self-join. LAG accesses the preceding row, LEAD the following row, both with a configurable offset parameter for shifts of more than one row and an optional default value for edge rows with no comparison basis. Combined with PARTITION BY, both functions work cleanly separated per group, which makes them the standard tool for month-over-month comparisons, trend analyses, and gap detection.

Compared to the classic self-join solution, LAG and LEAD offer clear advantages in performance and readability, because the database only has to sort the data once and process it in a single pass, instead of effectively doubling a table. Anyone who has so far solved before-after comparisons in SQL with self-joins should migrate those queries to LAG and LEAD as soon as the database version in use supports window functions.

11. FAQ: LAG and LEAD in SQL