SQL LAG and LEAD: comparing rows across time and sequence

Sometimes the important value is on another row

Many analytical questions are not about one row in isolation. They compare one event with another. Consider the following orders table, where each row represents one order:

Suppose we want to answer how much did each customer’s order value change compared with their previous order? For Asha, the sequence is July 1 (1500), July 10 (2200), July 31 (900). To calculate the change for the July 10 order, we need both the current order (2200) and the previous order (1500), so the difference is 2200 - 1500 = 700. The previous value lives on another row. This is exactly the kind of problem LAG is designed to solve.

LAG: look at an earlier row

LAG returns a value from a previous row in the analytical sequence. The basic form is LAG(value) OVER (ORDER BY sequence_column). Suppose we want to show each order beside the previous order amount:

SELECT
    order_id,
    customer,
    ordered_at,
    order_amount,
    LAG(order_amount) OVER (
        ORDER BY ordered_at, order_id
    ) AS previous_order_amount
FROM orders
ORDER BY
    ordered_at,
    order_id;

SQL has followed the overall chronological order. But that is not actually our requirement: we wanted the previous order for the same customer. For that, we need PARTITION BY.

LAG within each customer

SELECT
    order_id,
    customer,
    ordered_at,
    order_amount,
    LAG(order_amount) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
    ) AS previous_order_amount
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

Now the sequence restarts for each customer. For Asha, 1500 has no previous, then 2200 follows 1500, then 900 follows 2200. For Ben, 3200 has no previous, then 3600 follows 3200, then 5000 follows 3600.

PARTITION BY customer          Which rows belong to the same sequence?
ORDER BY ordered_at, order_id  What is the sequence within that customer?
LAG(order_amount)              Which earlier value should be returned?

Why the first row has NULL

For Asha’s first order (1001 | 1500), there is no earlier Asha order, so LAG(order_amount) returns NULL. The same happens for Ben’s first order. That NULL has a useful meaning: no previous row exists in this partition. It is not necessarily missing source data; it can be a natural consequence of the sequence.

Calculating change from the previous row

Once we have the previous value, we can compare it with the current value:

SELECT
    order_id,
    customer,
    order_amount,
    LAG(order_amount) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
    ) AS previous_order_amount,
    order_amount
        - LAG(order_amount) OVER (
            PARTITION BY customer
            ORDER BY ordered_at, order_id
        ) AS amount_change
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

For Asha’s second order, 2200 - 1500 = 700 (increased by 700); for her third, 900 - 2200 = -1300 (decreased by 1300). The pattern current_value - LAG(current_value) appears frequently and can measure price changes, balance changes, daily revenue changes, sensor changes, score changes, and differences between consecutive events.

Comparing percentage change

Sometimes the business wants the relative change, (current - previous) / previous. A CTE keeps the query readable:

WITH order_changes AS (
    SELECT
        order_id,
        customer,
        ordered_at,
        order_amount,
        LAG(order_amount) OVER (
            PARTITION BY customer
            ORDER BY ordered_at, order_id
        ) AS previous_order_amount
    FROM orders
)
SELECT
    order_id,
    customer,
    order_amount,
    previous_order_amount,
    100.0
        * (order_amount - previous_order_amount)
        / NULLIF(previous_order_amount, 0)
        AS percentage_change
FROM order_changes
ORDER BY
    customer,
    ordered_at,
    order_id;

NULLIF protects the calculation if the previous amount happens to be zero. For the first order in each partition, previous_order_amount is already NULL, so the percentage change also remains NULL.

Comparing event times with LAG

LAG is not limited to numeric values. To answer how much time passed between each customer’s orders, lag the timestamp itself and subtract:

SELECT
    order_id,
    customer,
    ordered_at,
    ordered_at
        - LAG(ordered_at) OVER (
            PARTITION BY customer
            ORDER BY ordered_at, order_id
        ) AS time_since_previous_order
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

This answers questions such as days since previous purchase, time since previous login, time between status changes, delay between sensor readings, and time between customer events. The same sequence logic applies whether the value is numeric, text, or temporal.

LEAD: look at a later row

LEAD is the opposite of LAG. LAG looks backward (previous row into current row); LEAD looks forward (current row into next row). Suppose we want the time of each customer’s next order:

SELECT
    order_id,
    customer,
    ordered_at,
    LEAD(ordered_at) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
    ) AS next_ordered_at
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

The final order for each customer has next_ordered_at = NULL because no later row exists.

LAG vs LEAD

The distinction is simple: LAG(value) asks what value came before this row, and LEAD(value) asks what value comes after this row. For a sequence A, B, C, D:

Looking more than one row away

Both functions can accept an offset. LAG(order_amount, 2) returns the value two rows before the current row. For 100, 200, 300, 400, the result is NULL, NULL, 100, 200. Similarly, LEAD(order_amount, 2) looks two rows forward. This is useful when comparisons are based on a fixed number of observations rather than only adjacent rows.

Providing a default value

LAG and LEAD can also accept a default value, LAG(value, offset, default). For example, LAG(order_amount, 1, 0) returns 0 when no previous row exists.

Comparing the previous row from the wrong sequence

Suppose the requirement is compare every order with the previous order for the same customer. Someone writes a valid query, but there is no PARTITION BY customer:

SELECT
    order_id,
    customer,
    order_amount,
    LAG(order_amount) OVER (
        ORDER BY ordered_at, order_id
    ) AS previous_order_amount
FROM orders;

-- A: Asha's July 1 order amount, 1500
-- B: Ben's July 2 order amount, 3200
-- C: NULL
-- D: SQL raises an error

The fix: define the correct partition

Add PARTITION BY customer so each customer’s sequence is independent: Asha runs 1500 -> 2200 -> 900 and Ben runs 3200 -> 3600 -> 5000. The previous row for Asha can never come from Ben’s partition.

Deterministic sequence matters

Suppose two orders for Asha have exactly the same ordered_at and the window contains only ORDER BY ordered_at. Which one is first? The query has not defined it, so LAG(...) or LEAD(...) could refer to different tied rows depending on execution. If order_id uniquely identifies the sequence among tied timestamps, use ORDER BY ordered_at, order_id. Previous and next are meaningful only when the sequence itself is well defined.

Finding the first value in a sequence

Sometimes we do not want the immediately previous value; we want the first value in the entire partition. Use FIRST_VALUE(...). To show every order together with the customer’s first order amount:

SELECT
    order_id,
    customer,
    ordered_at,
    order_amount,
    FIRST_VALUE(order_amount) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
    ) AS first_order_amount
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

Every Asha row carries first_order_amount = 1500 and every Ben row 3200. This makes comparisons against the beginning of a sequence easy, for example order_amount - FIRST_VALUE(order_amount) OVER (...) shows how far the current value has moved from the starting value.

LAST_VALUE: finding the last value

The natural counterpart is LAST_VALUE(...). To show every order with the customer’s latest order amount, you might write LAST_VALUE(order_amount) OVER (PARTITION BY customer ORDER BY ordered_at, order_id). This looks correct, but LAST_VALUE has an important window-frame trap.

The fix: define the full partition frame

Make the frame explicit:

SELECT
    order_id,
    customer,
    order_amount,
    LAST_VALUE(order_amount) OVER (
        PARTITION BY customer
        ORDER BY ordered_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders
ORDER BY
    customer,
    ordered_at,
    order_id;

The frame ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING means use the complete partition from its first row through its last row. This makes the intended meaning of LAST_VALUE explicit.

FIRST_VALUE vs LAG, LEAD vs LAST_VALUE

These answer different questions. For Asha’s third order (1500, 2200, 900 current), LAG(order_amount) returns 2200 (the immediately previous value) while FIRST_VALUE(order_amount) returns 1500 (the beginning of the partition). Similarly, LEAD looks one or more rows ahead, while LAST_VALUE looks at the end of the defined frame. Use LAG/FIRST_VALUE to compare with a nearby earlier row or the beginning of the sequence, and LEAD/LAST_VALUE to look ahead or at the end.

Detecting changes between events

A common event-data problem is return only rows where a value changed from the previous event. Suppose we have customer-status events:

Compare each status with the previous status using LAG(status) OVER (PARTITION BY customer ORDER BY event_at, event_id). Conceptually the previous status runs NULL, pending, pending, paid, paid, so the changes (pending -> paid, paid -> shipped) are easy to recognize. Because the window value is calculated before the outer filtering step, a CTE keeps the pattern clear:

WITH status_changes AS (
    SELECT
        event_id,
        customer,
        event_at,
        status,
        LAG(status) OVER (
            PARTITION BY customer
            ORDER BY event_at, event_id
        ) AS previous_status
    FROM status_events
)
SELECT
    event_id,
    customer,
    event_at,
    previous_status,
    status
FROM status_changes
WHERE previous_status IS DISTINCT FROM status
ORDER BY
    customer,
    event_at,
    event_id;

The exact null-safe comparison syntax varies across databases. In PostgreSQL, IS DISTINCT FROM is useful because it treats NULL as a comparable value. The broader pattern is: get the previous value with LAG, compare previous and current, and keep rows where they differ. This is useful for status transitions, price changes, configuration changes, account balance movements, state changes in event logs, and sensor transitions.

Finding the first and last event time

The same functions work with timestamps. FIRST_VALUE(ordered_at) and LAST_VALUE(ordered_at) (with the full UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame) let every row carry the first event time, current event time, and last event time. This helps answer how long after the first event this occurred, where in the customer’s lifecycle this event is, and how long the observed activity period is.

How to spot LAG, LEAD, and value problems

Think about LAG and LEAD when the requirement says previous order, next order, previous or next event, change from yesterday, difference from previous transaction, time since previous event, time until next event, status changed, or price increased or decreased. Think about FIRST_VALUE and LAST_VALUE when it says first value in the sequence, starting price, initial status, first event time, latest value, final status, or last event time.

Watch for these common mistakes:

• PARTITION BY is missing and comparisons cross customers.
• The ordering does not represent the real event sequence.
• Tied timestamps have no deterministic tie-breaker.
• A default value in LAG changes “no previous row” into a real business value.
• LAST_VALUE uses a frame that ends at the current row.
• Window-function results are filtered at the wrong query level.

Answer four questions for row-to-row comparisons

One more prediction

Consider Asha’s orders. For order 1005, what does LAG(order_amount) OVER (PARTITION BY customer ORDER BY ordered_at, order_id) return?

LAG(order_amount) OVER (
    PARTITION BY customer
    ORDER BY ordered_at, order_id
)

-- A: 1500
-- B: 2200
-- C: 900
-- D: NULL

Summary

Analytical SQL becomes especially powerful when one row needs context from another row. Use LAG(value) OVER (...) to look backward and LEAD(value) OVER (...) to look forward. LAG(order_amount) OVER (PARTITION BY customer ORDER BY ordered_at, order_id) means: within this customer’s orders, return the amount from the immediately previous order. That previous value can then measure change in amount, percentage change, time since previous event, and status transitions. Use FIRST_VALUE(value) when the comparison should be against the beginning of the sequence, and LAST_VALUE(value) when it should be against the end, defining the frame explicitly (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) when the complete partition is required.

Next, we will use these analytical patterns to solve deduplication and latest-row problems, including how to return exactly one current record per entity.