SQL Reference Guide

LEAD windowing function

Declaration

LEAD ( scalar_expression [ , offset ] [ , default ] ) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ partition_by_clause ] order_by_clause )

Description

LEAD accesses data from a subsequent row in the same result set without the use of a self-join.

Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

expression - defines the value the function returns from the specified row.
offset - a positive or negative value depending on before or after current row.
default - default is the value used when there is no row at the specified position.

order_by (required)
ROWS BETWEEN xxx AND yyy - ROWS and RANGE BETWEEN clauses may change the first row in the partition.

RANGE BETWEEN xxx AND yyy - ROWS and RANGE BETWEEN clauses may change the first row in the partition.

lag() and lead() return a value from a row that is before or after the current row in the current partition. lag() and lead() are synonyms except they treat the direction of the offset oppositely. That is, a negative offset value in lag() returns the same result as a positive offset in lead().

lag(expr, offset, default) == lead(expr, -offset, default)

A positive offset in lag( offset ) is the number of rows before the current row.
A negative offset in lag( offset ) is the number of rows after the current row.
A positive offset in lead( offset ) is the number of rows after the current row.
A negative offset in lead( offset ) is the number of rows before the current row.

Example

SELECT

Sale_Date,

Product_ID,

Price,

LEAD(Sale_Date, 1) OVER (

PARTITION BY Product_ID

ORDER BY Sale_Date

) AS Next_Sale_Date,

-- Calculate the difference in days between the next sale and the current sale

JULIANDAY(LEAD(Sale_Date, 1) OVER (

PARTITION BY Product_ID

ORDER BY Sale_Date

)) - JULIANDAY(Sale_Date) AS Days_Until_Next_Sale

FROM

Sales_History

WHERE

Product_ID = 'Product_B'

ORDER BY

Sale_Date;

Sale_Date Product_ID Price Next_Sale_Date Days_Until_Next_Sale

2024-03-01 Product_B 25.00 2024-03-05 4

2024-03-05 Product_B 26.50 2024-03-12 7

2024-03-12 Product_B 25.00 2024-04-01 20

2024-04-01 Product_B 27.00 NULL NULL