LEAD

The SQL LEAD() function is a window function that allows you to retrieve the value of a column from the next row within the same result set. This function is particularly useful when you need to compare the values of two consecutive rows in a table.

Syntax

The LEAD() function has the following syntax:

LEAD(column, offset, default) OVER (PARTITION BY partition_column ORDER BY sort_column)

column: specifies the column whose value you want to retrieve from the next row.
offset: specifies the number of rows ahead from the current row to retrieve the value from. The default value is 1, which means the next row.
default: specifies the value to return if the next row is not available. The default value is NULL.
PARTITION BY: divides the result set into partitions based on one or more columns. The LEAD() function is then applied to each partition separately.
ORDER BY: specifies the order in which the rows are sorted within each partition. The LEAD() function is applied in this order.

Example

Here is an example of how to use the LEAD() function to retrieve the value of the “sales” column from the next row:

SELECT 
product, sales, 
LEAD(sales) OVER (ORDER BY date) as next_sales
FROM sales_table;

In this example, the LEAD() function retrieves the value of the “sales” column from the next row, sorted by the “date” column. The result set will include a column named “next_sales” that contains the sales value from the next row.

You can also use the LEAD() function with a partitioning column to retrieve the value of the “sales” column from the next row within each partition:

SELECT region, product, sales, 
LEAD(sales) OVER (PARTITION BY region ORDER BY date) as next_sales
FROM sales_table;

In this example, the LEAD() function retrieves the value of the “sales” column from the next row, partitioned by the “region” column and sorted by the “date” column. The result set will include a column named “next_sales” that contains the sales value from the next row within each region.