LEAD(field[, offset[, default]])
LEAD assigns the value of the field column for the row that is specified by the offset. By default, LEAD() will assign the value NULL if the given row does not have a row offset rows after it within its window frame.
The user has the option to assign an alternate value under these conditions by including a value default.
A column that specifies the value to be assigned
This optional argument is an integer specifying from which row to take the value of the field column. If no offset is specified, the function assigns the value of the field column 1 row after the given row by default.
This optional argument specifies what value to return if the given row does not have a row offset rows after it within its window frame. If no default is specified, the value NULL is assigned.
The following example returns the subsequent salary for each employee within each department:
SELECT LEAD(Salary) OVER (PARTITION BY Department ORDER BY Salary) FROM Company.Employee
The following example specifies an offset and default argument to calculate the salary of the next employee within each department. If no such employee exists, the value 0 is returned:
SELECT LEAD(Salary, 1, 0) OVER (PARTITION BY Department ORDER BY Salary) FROM Company.Employee