LEAD()

Syntax

How it works

LEAD() is a window function that reaches forward: for each row it returns a value taken from a later row in the same ordered window. It is the exact mirror of LAG(), which reaches backward. By default it looks one row ahead, but the optional offset argument lets you jump any number of rows.

The order is set by the mandatory ORDER BY inside the OVER() clause: "following" only means something once the rows are sorted. An optional PARTITION BY splits the data into independent groups, so LEAD never crosses from one customer, product or account into the next. When there is no following row, LEAD returns the third argument if you supplied one, otherwise NULL.

Because LEAD keeps every input row instead of collapsing them, it is ideal for comparing each row with the next one: computing the gap to the next order, the time until the next event, or flagging the final row in a group. See the window functions guide for how OVER(), PARTITION BY and ORDER BY fit together, and ROW_NUMBER() for numbering rows within the same frame.

Examples

Look at the next row value

SELECT id, amount,
       LEAD(amount) OVER (ORDER BY id) AS next_amount
FROM orders
ORDER BY id;

id | amount | next_amount
---+--------+------------
 1 |    100 |         250
 2 |    250 |          90
 3 |     90 |        NULL

Time until the next order per customer

-- Days from each order to that customer's next order
SELECT customer_id, order_date,
       LEAD(order_date) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
       ) AS next_order_date
FROM orders
ORDER BY customer_id, order_date;

customer_id | order_date | next_order_date
------------+------------+----------------
          7 | 2026-01-03 | 2026-01-20
          7 | 2026-01-20 | 2026-02-11
          7 | 2026-02-11 | NULL

Days between an event and the next

SELECT device_id, logged_at,
       LEAD(logged_at) OVER (
         PARTITION BY device_id
         ORDER BY logged_at
       ) - logged_at AS gap_to_next
FROM sensor_events
ORDER BY device_id, logged_at;

device_id | logged_at  | gap_to_next
----------+------------+------------
     A1   | 2026-03-01 |     4 days
     A1   | 2026-03-05 |     2 days
     A1   | 2026-03-07 |        NULL

Flag the last row in each group

-- LEAD is NULL only on the final row of a partition
SELECT customer_id, order_date,
       CASE WHEN LEAD(order_date) OVER (
              PARTITION BY customer_id
              ORDER BY order_date
            ) IS NULL
            THEN 'yes' ELSE 'no' END AS is_last_order
FROM orders
ORDER BY customer_id, order_date;

customer_id | order_date | is_last_order
------------+------------+--------------
          7 | 2026-01-03 | no
          7 | 2026-01-20 | no
          7 | 2026-02-11 | yes

Jump two rows ahead with a default

-- offset 2 looks two rows forward; 0 fills the tail
SELECT id, amount,
       LEAD(amount, 2, 0) OVER (ORDER BY id) AS amount_in_2
FROM orders
ORDER BY id;

id | amount | amount_in_2
---+--------+------------
 1 |    100 |          90
 2 |    250 |          40
 3 |     90 |           0
 4 |     40 |           0

Common mistakes

-- No ORDER BY: which row is "next" is undefined,
-- so results are not reproducible
SELECT id,
       LEAD(amount) OVER () AS next_amount
FROM orders;

-- OVER() must contain an ORDER BY for LEAD to
-- have a meaningful notion of the following row
SELECT id,
       LEAD(amount) OVER (ORDER BY id) AS next_amount
FROM orders;

-- Window functions are not allowed in WHERE
SELECT customer_id, order_date
FROM orders
WHERE LEAD(order_date) OVER (
        PARTITION BY customer_id ORDER BY order_date
      ) IS NULL;

-- Compute LEAD in a CTE, then filter on the result
WITH ranked AS (
  SELECT customer_id, order_date,
         LEAD(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date
         ) AS next_date
  FROM orders
)
SELECT customer_id, order_date
FROM ranked
WHERE next_date IS NULL;

-- The last row returns NULL, which then breaks
-- arithmetic or downstream logic unexpectedly
SELECT id, amount,
       amount - LEAD(amount) OVER (ORDER BY id) AS diff
FROM orders;

-- Supply a default so the final row has a value
SELECT id, amount,
       amount - LEAD(amount, 1, 0) OVER (ORDER BY id) AS diff
FROM orders;

Performance

LEAD() is computed in a single ordered pass over each partition, so it is far cheaper than the self join it replaces. The dominant cost is the sort implied by ORDER BY inside OVER(). An index that matches the PARTITION BY and ORDER BY columns can let the engine read rows already in order and skip the sort entirely.

When several window functions share the same PARTITION BY and ORDER BY, most optimisers compute them over one shared window, so adding a matching LAG() or ROW_NUMBER() alongside LEAD is usually close to free. Keep their OVER() clauses identical so the planner can reuse the same sort.

Because LEAD retains every input row, filtering early still matters: apply your WHERE conditions before the window where possible, then compute LEAD, so the sort operates on the smallest set of rows. See the indexing guide for how ordered index scans avoid an explicit sort.

Interview questions

SELECT id,
       LEAD(amount, 2, 0) OVER (ORDER BY id) AS amount_in_2
FROM orders;

SELECT customer_id, order_date,
       LEAD(order_date) OVER (
         PARTITION BY customer_id ORDER BY order_date
       ) - order_date AS gap_to_next
FROM orders;