LEAD
Returns the value from the row that is offset rows after the current one in the window partition.
PostgreSQL 17.9
LEAD(column[, offset[, default]])- [ ]optional part
- column
- Column or expression whose value is taken from the next row
- offset
- How many rows ahead to look, 1 by default; a negative value looks back, like LAGintegerOptional
- default
- Value of the same type as column used if there is no row at that offset, NULL by defaultOptional
Return value
A value of the same type as column; default or NULL if there is no row at that offset, for example for the last row of a partition
Examples
Shift one and two rows forward
PostgreSQL 17.9
SELECT member_name,
birthday,
LEAD(member_name) OVER (
ORDER BY birthday
) AS next_member,
LEAD(member_name, 2, 'none') OVER (
ORDER BY birthday
) AS two_rows_ahead
FROM FamilyMembers;Time until the next purchase
PostgreSQL 17.9
SELECT family_member,
date,
LEAD(date) OVER (
PARTITION BY family_member
ORDER BY date
) AS next_date,
LEAD(date) OVER (
PARTITION BY family_member
ORDER BY date
) - date AS until_next
FROM Payments
ORDER BY family_member,
date;Details
The difference from the next row is LEAD(column) OVER (...) - column: for the last row of a partition it is NULL. default must be of the same type as column.