How to Use LEAD Function in PostgreSQL: Syntax and Examples

In PostgreSQL, the LEAD function lets you access data from the next row in your query result without using a self-join. You use it with OVER() to define the order and partition of rows, making it easy to compare current and next row values.

Syntax

The LEAD function syntax in PostgreSQL is:

• LEAD(expression [, offset [, default_value]]) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

Explanation:

• expression: The column or value you want from the next row.
• offset (optional): How many rows ahead to look (default is 1).
• default_value (optional): Value to return if there is no next row (default is NULL).
• OVER(): Defines the window for the function.
• PARTITION BY (optional): Divides rows into groups.
• ORDER BY: Defines the order of rows to find the next row.

sql

LEAD(expression [, offset [, default_value]]) OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

Example

This example shows how to use LEAD to get the next sale amount for each salesperson ordered by sale date.

sql

CREATE TABLE sales (
  salesperson TEXT,
  sale_date DATE,
  amount INT
);

INSERT INTO sales VALUES
('Alice', '2024-01-01', 100),
('Alice', '2024-01-05', 150),
('Bob', '2024-01-02', 200),
('Bob', '2024-01-06', 250);

SELECT
  salesperson,
  sale_date,
  amount,
  LEAD(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS next_sale_amount
FROM sales
ORDER BY salesperson, sale_date;

Output

 salesperson | sale_date  | amount | next_sale_amount 
-------------+------------+--------+------------------
 Alice       | 2024-01-01 |    100 |              150
 Alice       | 2024-01-05 |    150 |             NULL
 Bob         | 2024-01-02 |    200 |              250
 Bob         | 2024-01-06 |    250 |             NULL

Common Pitfalls

Common mistakes when using LEAD include:

• Not specifying ORDER BY inside OVER(), which leads to unpredictable results.
• Forgetting to use PARTITION BY when you want to reset the lead calculation for groups.
• Assuming LEAD returns a value even when there is no next row; it returns NULL unless a default is set.

sql

/* Wrong: Missing ORDER BY, results unpredictable */
SELECT salesperson, amount, LEAD(amount) OVER () AS next_amount FROM sales;

/* Right: Specify ORDER BY to get correct next row */
SELECT salesperson, amount, LEAD(amount) OVER (ORDER BY sale_date) AS next_amount FROM sales;

Quick Reference

Parameter	Description	Default
expression	Column or value to fetch from next row	Required
offset	Number of rows ahead to look	1
default_value	Value if no next row exists	NULL
PARTITION BY	Groups rows to apply LEAD separately	None
ORDER BY	Defines row order for LEAD	Required

Key Takeaways

Use LEAD with OVER(ORDER BY ...) to access next row values in a result set.

PARTITION BY lets you restart the lead calculation for each group.

Without ORDER BY, LEAD results are unpredictable.

LEAD returns NULL if no next row exists unless you provide a default value.

LEAD helps avoid complex self-joins for comparing rows.