SQLearnAcademy
←Function reference
WindowSQL standard

LEAD

Takes a value from the next row to compare with the current one.

Syntax

LEAD(<expression>) OVER (ORDER BY <sort key>)
LEAD(<expression>, <offset>, <default>) OVER (ORDER BY <sort key>)
LEAD(<expression>) OVER (PARTITION BY <group> ORDER BY <sort key>)

The mirror of LAG: it looks at the row below in the given order and returns a value from it. The offset is how many rows forward (1 by default), and the third argument is used when there's no next row. Handy for finding out what came “after”: when the next order arrived and how much time passed before it.

Data for the examples

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

orders

idcreated_atamount
12026-07-03 10:15:00120.50
22026-07-28 18:40:0080.25
32026-08-09 09:05:00199.99
42026-08-21 14:30:0045.00
52026-09-02 11:00:0064.75

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The date of the next order
SELECT id, created_at,
LEAD(created_at) OVER (ORDER BY created_at) AS next_order
FROM orders;
idcreated_atnext_order
12026-07-03 10:15:002026-07-28 18:40:00
22026-07-28 18:40:002026-08-09 09:05:00
32026-08-09 09:05:002026-08-21 14:30:00
42026-08-21 14:30:002026-09-02 11:00:00
52026-09-02 11:00:00NULL

The last order has no next one, so there's NULL there.

How much time until the next order
SELECT id, created_at,
LEAD(created_at) OVER (ORDER BY created_at) - created_at AS time_to_next
FROM orders;
idcreated_attime_to_next
12026-07-03 10:15:0025 days 08:25:00
22026-07-28 18:40:0011 days 14:25:00
32026-08-09 09:05:0012 days 05:25:00
42026-08-21 14:30:0011 days 20:30:00
52026-09-02 11:00:00NULL
The next one in the same department
SELECT name, department, salary,
LEAD(name) OVER (PARTITION BY department ORDER BY salary DESC) AS next_in_department
FROM employees;
namedepartmentsalarynext_in_department
Emmabackend1200Liam
Liambackend1000NULL
Sophiadesign1200NULL
Oliviafrontend1350Noah
Noahfrontend950NULL

PARTITION BY looks for the “next” one only inside the department: after Liam in backend there's nobody.

Watch out

For the last row the result is NULL. If you need another value, pass the third argument: LEAD(amount, 1, 0).

“Next” is defined by the ORDER BY in OVER, not the ORDER BY at the end of the query. Sort the result differently, and the next row stays the same.

You can't use the result in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

LAGFIRST_VALUELAST_VALUE