365 Days of Daily Coding: Day 113

LAG and LEAD are window functions that is always followed by an OVER clause. LAG function returns the preceeding value from the current row whereas LEAD function returns the succeeding value from the current row.

The syntax for both LAG and LEAD are as follows:

LAG(expression [,offset[,default_value]]) OVER(ORDER BY columns)

LEAD(expression [,offset[,default_value]]) OVER(ORDER BY columns)

Both the function takes three arguments: the name of a column or an expression, the number of rows to offset and the default value if the row is found to be empty.

Following are the scenarios where the functions will be helpful. They were taken from this excellent article.

1. Using LAG() and LEAD() to Compare Values
2. Using LAG() and LEAD() With a Specific Offset
3. Using LAG() and LEAD() With a Default Value
4. Using LAG() and LEAD() With Partitions