LEAD()
SQL · Window Functions
Theory
LEAD(column, offset, default) is the mirror of LAG: it returns the value from a row that is offset rows after the current row, in the window ORDER BY order. With offset 1 it is 'the next row's value'. The last row of each partition has no next row, so LEAD returns NULL there, or the default you give.
LEAD is handy whenever a row should know what happens next. Ordered by order_date and partitioned by customer_id, LEAD(order_date) gives each order the date of that customer's next order. The gap between the two is the time to repeat purchase, and a NULL next date marks the customer's latest order. It is also used to build validity ranges, such as 'this price is valid from this row's date until the next row's date'. Like LAG, it ignores the window frame.
Why we use it
Some questions look forward: when did the customer come back, how long did a status last, until when was this price valid. LEAD fetches the next value onto the current row so those gaps become simple arithmetic.
When to use it
Use LEAD when you need a value from a later row, such as the next event or the end of a period. Use LAG for earlier rows. A NULL from LEAD is a quick way to spot the last row of each group.
How it works
- Rows split by PARTITION BY and sorted by ORDER BY
- Take the current row
- Step forward 'offset' rows within the same partition
- Does that row exist? Yes: Return its column value. No: Return the default (NULL if none given).
- Use it, e.g. next date minus this date
- Next value added to each row
Syntax
LEAD(column, offset, default) OVER (ORDER BY col)Example
SELECT name, salary,
LEAD(salary, 1) OVER (ORDER BY id) AS next_salary
FROM employees;Key points
- Mirror image of LAG(): looks forward instead of back
- Returns NULL (or the default) on the last row of each partition
- Useful for gaps to the next event or validity end dates
- LEAD(col, 2) looks two rows ahead
- Ignores frames; only partition and order matter