LEAD & LAG Functions

LEAD and LAG are window functions that let you access data from the following or preceding row, relative to the current row, within an ordered set.

LEAD

Returns data from the next row after the current one.

Example: To see the age of the next older student:

LEAD(age) OVER (ORDER BY age DESC)

LAG

Returns data from the previous row before the current one.

Example: To see the age of the next younger student:

LAG(age) OVER (ORDER BY age DESC)

Example Query

SELECT name, age,
LEAD(age) OVER (ORDER BY age DESC) AS next_older_age,
LAG(age) OVER (ORDER BY age DESC) AS next_younger_age
FROM students;

This helps compare each row with its neighbors.

‍