SQL LAG and LEAD Functions Guide

SQL LAG and LEAD are two of the most useful window functions because they let you compare a row to the row before it or the row after it without writing complicated self-joins.

That matters because a lot of real SQL work involves questions like:

• what was the previous order amount for this customer?
• how much did revenue change compared to last month?
• what was the next status after this event?
• when did the value first change?
• how many days passed between one row and the next?
• what is the difference between the current reading and the previous reading?

These are all sequence-based questions.

And that is exactly what LAG and LEAD are built for.

If you understand them well, many analytical SQL problems become much easier to solve and much easier to read.

This guide explains SQL LAG and LEAD clearly, including:

• what they do
• how the syntax works
• why ORDER BY matters so much
• how PARTITION BY changes the result
• how to use offsets and default values
• common analytical patterns
• and the mistakes people make most often

Why LAG and LEAD matter

A lot of SQL analysis is not only about a single row. It is about the relationship between rows in a sequence.

Examples:

• sales this month versus last month
• a user’s current event versus their prior event
• the next scheduled payment after the current one
• the previous inventory count before a stock change
• the time between consecutive logins
• the next page a user visited in a session

You can solve some of these problems with self-joins. But self-joins usually become:

• harder to read
• harder to maintain
• and more error-prone

LAG and LEAD are often better because they express the real intention directly:

• give me the previous row value
• or give me the next row value

That is why they are such high-value SQL functions.

The most important rule

Before anything else, remember this:

LAG and LEAD only make sense when the row order is clearly defined.

That is the single most important rule in this topic.

If you do not define the right ORDER BY in the window, then:

• “previous row”
• and “next row”

do not really mean anything useful.

So whenever you use LAG or LEAD, the first question should be:

• what is the sequence I care about?

Examples:

• by date
• by timestamp
• by event order
• by rank
• by invoice date
• by ID if ID truly reflects sequence

Once you define that correctly, the functions become very powerful. If you define it badly, the results can look valid but mean the wrong thing.

What LAG does

LAG returns a value from an earlier row in the window.

In simple terms:

• it looks backward

If you order rows by date, LAG can show:

• the previous date’s value
• the previous event
• the previous measurement
• the previous salary
• or any other earlier row value in that sequence

That is why LAG is often used for:

• change detection
• previous value comparison
• running differences
• trend analysis
• and event-sequence logic

What LEAD does

LEAD returns a value from a later row in the window.

In simple terms:

• it looks forward

If you order rows by date, LEAD can show:

• the next date’s value
• the next event
• the next payment
• the next state change
• or any other later row value in that sequence

That makes LEAD useful for:

• next-step analysis
• identifying future transitions
• measuring time until the next event
• and forecasting-like sequence comparisons

The easiest way to remember them

A simple memory trick is:

• LAG looks backward
• LEAD looks forward

Or:

• LAG = previous row
• LEAD = next row

That is the quickest way to keep them straight.

Basic syntax of LAG

The general pattern is:

LAG(expression, offset, default_value) OVER (
    PARTITION BY ...
    ORDER BY ...
)

Not every part is required, but the pieces mean:

• expression = the column or value you want from the earlier row
• offset = how many rows backward to look
• default_value = what to return if no earlier row exists
• PARTITION BY = optional grouping of separate sequences
• ORDER BY = the row order inside each partition

The most important required idea is usually the ordering.

Basic syntax of LEAD

The general pattern is:

LEAD(expression, offset, default_value) OVER (
    PARTITION BY ...
    ORDER BY ...
)

The structure is the same as LAG.

The difference is only direction:

• LAG looks backward
• LEAD looks forward

That symmetry is one reason these functions are so easy to learn together.

A simple example table

Suppose you have monthly sales:

month	revenue
2026-01-01	1000
2026-02-01	1200
2026-03-01	900
2026-04-01	1500

Now you want to see each month’s revenue along with the previous month’s revenue.

Basic LAG example

SELECT
    month,
    revenue,
    LAG(revenue) OVER (
        ORDER BY month
    ) AS previous_revenue
FROM monthly_revenue;

Result conceptually:

month	revenue	previous_revenue
2026-01-01	1000	NULL
2026-02-01	1200	1000
2026-03-01	900	1200
2026-04-01	1500	900

This is one of the most common LAG use cases.

The first row gets NULL because there is no earlier row.

Basic LEAD example

Now suppose you want the next month’s revenue.

SELECT
    month,
    revenue,
    LEAD(revenue) OVER (
        ORDER BY month
    ) AS next_revenue
FROM monthly_revenue;

Result conceptually:

month	revenue	next_revenue
2026-01-01	1000	1200
2026-02-01	1200	900
2026-03-01	900	1500
2026-04-01	1500	NULL

The last row gets NULL because there is no next row.

That is basic LEAD behavior.

Why ORDER BY matters so much

The ORDER BY inside the window definition tells SQL what “previous” and “next” mean.

For example:

LAG(revenue) OVER (ORDER BY month)

means:

• previous row by month order

If you changed the ordering, you would change the meaning of the previous row.

That is why LAG and LEAD are only as good as the sequence you define.

If the order is wrong:

• the previous value is wrong
• the next value is wrong
• and every downstream comparison becomes misleading

This is the single biggest correctness issue with these functions.

Using PARTITION BY

PARTITION BY separates the data into independent groups before LAG or LEAD is applied.

That means:

• previous and next values are found inside each partition only

This is extremely useful.

For example, suppose you want to compare each customer’s current order to their previous order. You do not want one customer’s previous row to come from another customer.

That is what PARTITION BY prevents.

Example: previous order amount per customer

Suppose you have:

customer_id	order_date	total_amount
1	2026-01-10	100
1	2026-02-14	150
1	2026-03-18	90
2	2026-01-20	200
2	2026-03-01	250

Now use LAG:

SELECT
    customer_id,
    order_date,
    total_amount,
    LAG(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS previous_order_amount
FROM orders;

Now each customer’s sequence is separate.

That means:

• customer 1’s previous value comes from customer 1 only
• customer 2’s previous value comes from customer 2 only

This is one of the most important real-world uses of PARTITION BY.

Without PARTITION BY, results can be wrong

If you wrote:

LAG(total_amount) OVER (ORDER BY order_date)

then the previous order might come from a different customer entirely.

That may be valid for some questions. But for “previous order per customer,” it is clearly wrong.

So a good habit is:

• ask whether the sequence should restart for each entity

If yes, you probably need PARTITION BY.

Offsets in LAG and LEAD

By default:

• LAG(expression) means one row back
• LEAD(expression) means one row forward

But you can change that by supplying an offset.

Example: LAG by 2 rows

SELECT
    month,
    revenue,
    LAG(revenue, 2) OVER (
        ORDER BY month
    ) AS revenue_two_months_ago
FROM monthly_revenue;

This looks two rows backward instead of one.

Example: LEAD by 2 rows

SELECT
    month,
    revenue,
    LEAD(revenue, 2) OVER (
        ORDER BY month
    ) AS revenue_two_months_ahead
FROM monthly_revenue;

This looks two rows forward.

Offsets are useful when you want:

• week-over-week and two-weeks-back comparisons
• next event and second-next event
• prior reading and prior-two-reading comparisons

Default values in LAG and LEAD

If there is no previous row for LAG, or no next row for LEAD, the default result is usually NULL.

But you can provide a default value.

Example: LAG with default

SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (
        ORDER BY month
    ) AS previous_revenue
FROM monthly_revenue;

Now the first row gets 0 instead of NULL.

This can be useful in some calculations, but you should use it carefully.

Why?

Because NULL often has a meaningful interpretation:

• there is no earlier row

Replacing it with 0 changes that meaning.

So defaults should reflect the real business logic, not just convenience.

Calculating row-to-row differences with LAG

One of the most common uses of LAG is calculating change between the current row and the previous row.

Example:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (
        ORDER BY month
    ) AS previous_revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM monthly_revenue;

This gives you:

• current revenue
• previous revenue
• difference between them

That is a very practical pattern for:

• financial reporting
• usage trends
• inventory changes
• temperature changes
• performance comparisons

Calculating percentage change

You can also calculate percentage change.

Example:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (
        ORDER BY month
    ) AS previous_revenue,
    (
        revenue - LAG(revenue) OVER (ORDER BY month)
    ) * 100.0
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS pct_change
FROM monthly_revenue;

This is useful for:

• month-over-month growth
• week-over-week trends
• conversion rate change
• traffic growth
• revenue movement

The NULLIF(..., 0) pattern helps avoid division by zero.

Using LEAD to measure time until the next event

LEAD is especially useful when you want to compare the current row with the next row in a timeline.

Suppose you have user login events and want to measure the time until the next login.

SELECT
    user_id,
    login_time,
    LEAD(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
    ) AS next_login_time
FROM user_logins;

Then you can calculate the gap:

SELECT
    user_id,
    login_time,
    LEAD(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
    ) AS next_login_time,
    LEAD(login_time) OVER (
        PARTITION BY user_id
        ORDER BY login_time
    ) - login_time AS time_until_next_login
FROM user_logins;

This is very useful in:

• session analysis
• usage interval analysis
• machine events
• sensor readings
• workflow timing

Using LAG to detect state changes

Another powerful use case is change detection.

Suppose you have a table of daily subscription status snapshots.

You can compare the current status to the previous status:

SELECT
    user_id,
    snapshot_date,
    status,
    LAG(status) OVER (
        PARTITION BY user_id
        ORDER BY snapshot_date
    ) AS previous_status
FROM subscription_status_history;

Then you can identify rows where the status changed:

SELECT *
FROM (
    SELECT
        user_id,
        snapshot_date,
        status,
        LAG(status) OVER (
            PARTITION BY user_id
            ORDER BY snapshot_date
        ) AS previous_status
    FROM subscription_status_history
) t
WHERE status <> previous_status
   OR previous_status IS NULL;

This is extremely useful for:

• churn analysis
• workflow state transitions
• account tier changes
• ticket status movement
• inventory status changes

Using LEAD to see the next state

You can also look forward.

Example:

SELECT
    user_id,
    snapshot_date,
    status,
    LEAD(status) OVER (
        PARTITION BY user_id
        ORDER BY snapshot_date
    ) AS next_status
FROM subscription_status_history;

This helps answer:

• what happened next after this state?
• what was the next page or event?
• what was the next status after pending?
• what comes immediately after each action?

That is very useful in event-sequence analysis.

LAG and LEAD in event-stream analysis

For event streams, these functions are especially powerful.

Examples:

• previous page viewed
• next page viewed
• previous ticket status
• next support event
• prior product viewed
• next checkout step
• previous sensor reading
• next machine state

These are all sequence-based problems, and LAG and LEAD are built for them.

This is one of the reasons they are so valuable for:

• analytics engineers
• data analysts
• backend developers
• and data engineers

LAG and LEAD versus self-joins

Before window functions became more widely used, people often solved these problems with self-joins.

That approach can still work. But LAG and LEAD are usually better when the real question is:

• previous row
• or next row

Why?

Because they are:

• more readable
• more direct
• easier to maintain
• and usually easier to reason about

A self-join says:

• join this table to itself and somehow identify the prior row

LAG says:

• give me the prior row value

That difference in clarity is huge.

Common mistakes with LAG and LEAD

There are a few mistakes that show up often.

1. Wrong ORDER BY

This is the biggest one.

If the ordering is wrong, then:

• previous row is wrong
• next row is wrong
• comparisons are wrong

Always verify that the ordering matches the real sequence you care about.

2. Forgetting PARTITION BY

If the sequence should restart per:

• customer
• user
• account
• product
• session

then you probably need PARTITION BY.

Without it, rows from different groups can bleed into one another.

3. Ignoring ties in ordering

If two rows have the same timestamp or same sort key, then the order between them may be ambiguous unless you add a tie-breaker.

For example, this may be unsafe:

ORDER BY event_time

if many rows can share the same event_time.

A better pattern may be:

ORDER BY event_time, event_id

This gives a stable order.

That is a very important real-world detail.

4. Using a misleading default value

Replacing NULL with 0 or some other default can change the meaning of the result.

Sometimes that is correct. Sometimes it is not.

Always ask:

• does this default represent real business meaning?

5. Repeating the same window expression too much

In some queries, you may end up writing the same LAG(...) OVER (...) expression several times.

That can hurt readability.

A cleaner pattern is often:

• compute it once in a CTE or subquery
• then reference it in the outer query

This is easier to maintain.

A cleaner pattern using a CTE

Instead of this:

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM monthly_revenue;

you can write:

WITH revenue_with_previous AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (
            ORDER BY month
        ) AS previous_revenue
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    previous_revenue,
    revenue - previous_revenue AS revenue_change
FROM revenue_with_previous;

This is usually easier to read and extend.

Performance thoughts

LAG and LEAD are powerful, but like any window function, they still depend on:

• the amount of data
• the partition size
• the sort requirement
• and the overall query plan

They often require the database to:

• organize rows by the window order
• and evaluate the function over that ordered result

So while they are usually cleaner than self-joins for these problems, you should still pay attention to:

• row volume
• partition size
• indexing that supports the sort path where relevant
• and execution plans for large production queries

The main point is:

• use them for clarity and correctness
• then measure if performance matters

Real-world use cases for LAG and LEAD

These functions appear in many practical scenarios.

Finance and business reporting

• month-over-month revenue changes
• prior invoice value comparison
• customer spend trend analysis

Product analytics

• previous event in a funnel
• next page in a session
• time between user actions

Operations

• previous inventory count
• time to next shipment step
• machine reading comparison

Customer support

• previous ticket status
• next escalation step
• time between replies

Data engineering

• deduplication tie-breaking context
• event stream sequencing
• change history reconstruction

This is why LAG and LEAD are such high-value SQL tools.

FAQ

What do LAG and LEAD do in SQL?

LAG returns a value from an earlier row in the window, while LEAD returns a value from a later row. They are used to compare the current row to previous or next rows without writing self-joins.

What is the difference between LAG and LEAD?

LAG looks backward in the ordered window, and LEAD looks forward. Both use the same general syntax and are commonly used for trend analysis, change tracking, and sequence comparisons.

Do LAG and LEAD require ORDER BY?

In practice, yes. Without a meaningful ORDER BY inside the window definition, there is no reliable idea of which row is previous or next, so the results may be logically meaningless.

When should I use LAG and LEAD instead of a self-join?

Use LAG and LEAD when you need previous-row or next-row comparisons within an ordered sequence, especially for time-series data, transitions, rankings, or event streams. They are usually cleaner and easier to maintain than self-joins for these patterns.

Final thoughts

SQL LAG and LEAD are some of the most useful window functions because they let you compare neighboring rows directly.

That makes them ideal for:

• previous-value analysis
• next-step analysis
• trend tracking
• change detection
• timeline comparisons
• and sequence-based reporting

The most important things to remember are:

• LAG looks backward
• LEAD looks forward
• ORDER BY defines what previous and next mean
• PARTITION BY keeps sequences separate by group
• offsets let you move more than one row away
• and defaults should only be used when they match real business meaning

If you understand those ideas clearly, these functions stop feeling advanced and start feeling like one of the most natural tools in analytical SQL.