SQL LEAD()

The LEAD() window function takes a column and an integer offset as arguments and returns the value of the cell in that column that is the specified number of rows after the current row. A third argument can be added to fill cells that do not have a corresponding row.

Syntax

LEAD(column1_name, offset) OVER(
  ORDER BY column2_name
)

• The ORDER BY clause is required.

• There is also a third, optional argument that sets the value of cells where the corresponding row does not exist. The third argument will be NULL by default.

• When using LEAD() with partitions, any cells with a corresponding row in another partition will be filled with the default value. Think of it as treating each partition as a separate table.

Example

Suppose there’s a yearly_sales table that looks like this:

Running the following query shows the next month’s sales for every month, with the last month having NA as its value.

SELECT month,
  sales,
  LEAD(sales, 1, "NA") OVER(
    ORDER BY month
  ) AS next_month_sales
FROM yearly_sales;

Copy to clipboard
Copy to clipboard

This will be the output: