LEAD()

Look ahead to the next row's value — the mirror of LAG

Advanced 10 minLEADwindownext

Looking at the Next Row

LEAD() retrieves a value from the next row in the window — the mirror image of LAG. Where LAG looks backward, LEAD looks forward in the ordered sequence. Here's what it enables:

Forward-looking analysis — While LAG answers "what was the previous value?", LEAD answers "what comes next?" Both take the same three arguments: column, offset (default 1), and default value (default NULL).

Gap detection — LEAD(event_date) OVER (ORDER BY event_date) - event_date computes the time gap between each event and the next one. Essential for analyzing session durations, order intervals, and activity patterns.

Terminal detection — LEAD returns NULL for the last row in a partition because there is no next row to look at. This makes it easy to identify the final action in a sequence or the most recent event per group.

Same three arguments as LAG — Column to retrieve, offset (how far ahead to look), and a default value when there's no subsequent row. LEAD(price, 1, 0) returns 0 instead of NULL for the last row.

Knowledge Canvas

How LEAD Works

Look ahead to the next row

LEAD(col, n, default) looks forward n rows
Mirror image of LAG — same syntax, opposite direction
Last row in partition → returns NULL (or default)
ORDER BY defines what "next" means

LAG vs LEAD

Two sides of the same coin

LAG: look backward

LEAD: look forward

Previous row value

Next row value

First row → NULL

Last row → NULL

Common: past comparisons

Common: future predictions

LEAD Patterns

Looking ahead in data

Next event timing: LEAD(event_date) - event_date
Duration until next action: LEAD(timestamp) OVER (ORDER BY timestamp)
Is this the last event? LEAD(id) IS NULL
Combine LAG + LEAD for both directions at once

Real-World Uses

When LEAD shines

Time to next purchase / event
Forecasting: compare current to upcoming
Marking terminal events in sequences
Joining events with their subsequent state

Syntax

Syntax Template

1-- Next row value (1 row ahead by default)

2LEAD(column) OVER (

3 ORDER BY col

6-- With offset and default for the last row

7LEAD(column, 1, default_value) OVER (

8 ORDER BY col

LEAD(col)Next row value (1 row ahead by default)

LEAD(col, n)Value from n rows ahead

Last rowLEAD returns NULL (or default) for the last row

Sample Data

orders

12 rows

id	order_date	total
5001	2024-01-15	59.98
5002	2024-02-08	449
5003	2024-02-20	12.5
5004	2024-03-05	89.99
5005	2024-03-15	245
5006	2024-03-22	159
5007	2024-04-02	45.5
5008	2024-04-12	79
5009	2024-04-22	425
5010	2024-05-05	18.99
5011	2024-05-18	134
5012	2024-06-08	65.5

products

10 rows

id	name	category	price	stock
P101	Wireless Mouse	Electronics	24.99	150
P102	Office Desk	Furniture	189	25
P103	LED Desk Lamp	Furniture	45.5	80
P104	USB-C Cable	Electronics	12.99	500
P105	Bluetooth Speaker	Electronics	79	60
P106	Office Chair	Furniture	95	15
P107	Coffee Maker	Appliances	89.99	40
P108	Notebook Set	Stationery	8.5	200
P109	Wireless Headphones	Electronics	159	30
P110	Standing Desk	Furniture	425	8

Worked Example

Show each order with the next order date and the gap in days.

SQL

1SELECT

2 order_date,

3 total,

4 LEAD(order_date) OVER (ORDER BY order_date) AS next_order_date,

5 JULIANDAY(LEAD(order_date) OVER (ORDER BY order_date) ) - JULIANDAY(order_date) AS days_until_next

6FROM orders

7ORDER BY

8 order_date

9LIMIT 6;

LEAD(order_date) gives the next order's date. JULIANDAY converts dates to numbers so we can subtract them to get the gap in days. The last row has NULL for next_order_date because there is no subsequent row.

Output

5 rows

order_date	total	next_order_date	days_until_next
2024-01-15	59.98	2024-02-08	24
2024-02-08	449	2024-02-20	12
2024-02-20	12.5	2024-03-05	14
2024-03-05	89.99	2024-03-15	10
2024-03-15	245	2024-03-22	7

Key Concepts

1LEAD is the mirror of LAG — looks forward instead of back

2Last row returns NULL (no next row to look at)

3LEAD(date) - date = time gap until next event

4NULL from LEAD signals the final item in a sequence

Pro Tip

Looking ahead is just as important as looking back. LEAD enables gap analysis, forward predictions, and identifying terminal events in sequences.

When to Use

Time between events, identifying last action before churn, forward-looking comparisons, gap detection in sequential data.

Challenge

Solve the problem below

For each product (ordered by price ascending), show the name, price, and the next higher price (call it next_price). Show all 10 products.

Your Query

LAG()

UNION & UNION ALL