LEAD

Window Function

Description

The LEAD window function returns the values for a row at a given offset below (after) the current row in the partition.

(Redshift SQL Language Reference LEAD window function)

This function is partially supported in Snowflake. In Redshift the offset can be a constant integer or an expression that evaluates to an integer. In Snowflake it has a limitation that it can only be a constant.

For more information about quoted identifiers in functions, click here.

Grammar Syntax

LEAD (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

Sample Source Patterns

Setup data

Redshift

CREATE TABLE sales (
    sale_id INT,
    customer_id INT,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales (sale_id, customer_id, sale_date, sale_amount)
VALUES
    (1, 301, '2024-01-01', 150.00),
    (2, 301, '2024-01-02', NULL),
    (3, 301, '2024-01-03', 250.00),
    (4, 301, '2024-01-04', 350.00),
    (5, 302, '2024-02-01', 100.00),
    (6, 302, '2024-02-02', 200.00),
    (7, 302, '2024-02-03', NULL),
    (8, 302, '2024-02-04', 300.00);

Input Code:

Redshift

SELECT
    LEAD(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead,
    LEAD(sale_amount, 2) RESPECT NULLS OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead_respect_null,
    LEAD(sale_amount, 1) IGNORE NULLS OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead_ignore_nulls
FROM sales;

Result

LEAD	LEAD_RESPECT_NULL	LEAD_IGNORE_NULLS
200.00	null	200.00
null	300.00	300.00
300.00	null	300.00
null	null	null
null	250.00	250.00
250.00	350.00	250.00
350.00	null	350.00
null	null	null

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
    LEAD(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead,
    LEAD(sale_amount, 2) RESPECT NULLS OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead_respect_null,
    LEAD(sale_amount, 1) IGNORE NULLS OVER (PARTITION BY customer_id ORDER BY sale_date) AS lead_ignore_nulls
FROM sales;

Result

LEAD	LEAD_RESPECT_NULL	LEAD_IGNORE_NULLS
200.00	null	200.00
null	300.00	300.00
300.00	null	300.00
null	null	null
null	250.00	250.00
250.00	350.00	250.00
350.00	null	350.00
null	null	null

Known Issues

No issues were found.

Related EWIs

There are no known issues.