PostgreSQL - LEAD Function

In PostgreSQL, the LEAD() function is a powerful window function used to access a row that follows the current row at a specific physical offset. This function is generally employed to compare the value of the current row with the value of the next row following the current row.

Let us better understand the LEAD Function in PostgreSQL from this article.

Syntax

LEAD(expression [, offset [, default_value]]) 
OVER (
 [PARTITION BY partition_expression, ... ]
 ORDER BY sort_expression [ASC | DESC], ...
)

Parameters

Let's analyze the above syntax:

• expression: This can be a column, expression, or subquery that evaluates to a single value.
• offset: A positive integer that specifies the number of rows to move forward from the current row. It can be an expression, subquery, or column. If the offset is not set, it defaults to 1.
• default_value: This is returned if the leaded row does not exist. If not specified, it defaults to NULL.
• PARTITION BY: Divides rows into partitions. By default, it considers the query result as a single partition.
• ORDER BY: Sorts the query result rows in each partition.

PostgreSQL LEAD Function Examples

Let us take a look at some of the examples of the LEAD Function in PostgreSQL to better understand the concept.

Let’s set up a new table for the demonstration named 'Match':

Example 1: Using LEAD() for Yearly Averages

The below query uses the LEAD() function to return the overs of the current year and the average overs per year.

Query:

WITH cte AS (
 SELECT year, 
 SUM(overs) overs
 FROM Match
 GROUP BY year
 ORDER BY year
) 
SELECT
 year, 
 overs,
 LEAD(overs, 1) OVER (
 ORDER BY year
 ) year_average
FROM
 cte;

Output:

Example 2: Comparing Overs by Year and Match ID

The following statement uses the LEAD() function to compare the overs of the current year with overs of the next year for each group.

Query:

SELECTyear, 
 overs,
 match_id,
 LEAD(overs, 1) OVER (
  PARTITION BY match_id
 ORDER BY year
 ) next_year_overs
FROM
 Match;

Output:

Important Points About PostgreSQL LEAD Function

• If there is no row at the specified offset, the LEAD() function returns NULL by default.
• The LEAD() function will include NULL values in its calculations. If there are NULL values in the ordered column, they will be treated as any other value.
• The LEAD() function can be used within CTEs to simplify complex queries and make them more readable.
• Combining LEAD() and LAG() can help you analyze trends both forward and backward.