SQL LEAD(): Look Ahead to the Next Row

LEAD() is the mirror image of LAG. Instead of looking back, it looks forward: it returns a value from the next row (or one several rows ahead). It is perfect for questions like how long until the next order? or what happened next?

Syntax#

LEAD(column, offset, default) OVER (
  [PARTITION BY column]
  ORDER BY column
)

The arguments work exactly like LAG's: offset defaults to 1 and default to NULL.

Next month's revenue#

SELECT month, revenue,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_sales
WHERE region = 'North'
ORDER BY month;

month   | revenue | next_month
--------+---------+-----------
2026-01 | 120     | 150
2026-02 | 150     | 110
2026-03 | 110     | 170
2026-04 | 170     | NULL

The last row has nothing after it, so LEAD returns NULL.

Time until the next event#

LEAD shines with dates. The gap between each order and the next:

-- SQLite date arithmetic; see Date Functions for other databases
SELECT order_id,
       order_date,
       LEAD(order_date) OVER (ORDER BY order_date) AS next_order,
       CAST(julianday(LEAD(order_date) OVER (ORDER BY order_date))
            - julianday(order_date) AS INTEGER) AS days_until_next
FROM orders
ORDER BY order_date;

order_id | order_date | next_order | days_until_next
---------+------------+------------+----------------
101      | 2026-01-05 | 2026-02-11 | 37
102      | 2026-02-11 | 2026-02-20 | 9
103      | 2026-02-20 | 2026-03-02 | 10
104      | 2026-03-02 | NULL       | NULL

In PostgreSQL the last column is simply LEAD(order_date) OVER (ORDER BY order_date) - order_date. Add PARTITION BY customer_id to measure the gap between each customer's own orders, a common churn and retention metric.

Who came next?#

LEAD can return any column, not just numbers:

SELECT name, hire_date,
       LEAD(name) OVER (ORDER BY hire_date) AS next_hire
FROM employees
ORDER BY hire_date;

name  | hire_date  | next_hire
------+------------+----------
Meera | 2018-01-10 | Ravi
Ravi  | 2019-07-01 | Priya
Priya | 2020-05-04 | Asha
Asha  | 2021-03-15 | John
John  | 2022-11-20 | NULL

Spotting an upcoming drop#

Months after which a region's revenue fell:

SELECT region, month, revenue
FROM (
  SELECT region, month, revenue,
         LEAD(revenue) OVER (PARTITION BY region ORDER BY month) AS nxt
  FROM monthly_sales
) t
WHERE nxt < revenue
ORDER BY region, month;

region | month   | revenue
-------+---------+--------
North  | 2026-02 | 150
South  | 2026-03 | 130

North fell from 150 in February to 110 in March; South from 130 in March to 125 in April.

LEAD vs LAG#

LEAD(x) OVER (ORDER BY d) gives the same values as LAG(x) OVER (ORDER BY d DESC); use whichever reads more naturally.

Common mistakes#

Practice#

SELECT region, month, revenue,
       LEAD(revenue) OVER (PARTITION BY region ORDER BY month) AS next_month
FROM monthly_sales
ORDER BY region, month;

region | month   | revenue | next_month
-------+---------+---------+-----------
North  | 2026-01 | 120     | 150
North  | 2026-02 | 150     | 110
North  | 2026-03 | 110     | 170
North  | 2026-04 | 170     | NULL
South  | 2026-01 | 90      | 90
South  | 2026-02 | 90      | 130
South  | 2026-03 | 130     | 125
South  | 2026-04 | 125     | NULL

Key takeaway#