PostgreSQL lead() Function

The PostgreSQL lead() function returns the value from the specified row after the current row in the partition where the current row is located.

`lead()` Syntax

Here is the syntax of the PostgreSQL lead() function:

lead(expr[, offset[, default]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

Parameters

expr: Required. It can be a column name or an expression.
offset: Optional. The number of lines at the offset relative to the current line. The default value is 1.
default: Optional. It can be a column name or an expression.
partition_column_list: List of columns for partitioning.
partition_column_list: List of columns for sorting.

Return value

The PostgreSQL lead() function returns the value from the specified row after the current row in the partition where the current row is located.

`lead()` Examples

Preparing Data

Use the following CREATE TABLE statement to create a table named tax_revenue to store tax returns for every quarter:

CREATE TABLE tax_revenue (
  id SERIAL PRIMARY KEY,
  year CHAR(4) NOT NULL,
  quarter CHAR(1) NOT NULL,
  revenue INT NOT NULL
);

This tax_revenue table has 4 columns as following:

id - The row ID, primary key.
year- The year.
quarter- The quarter of the year, from 1 to 4.
revenue- Tax revenue.

Insert some rows into the tax_revenue table using the following INSERT statement:

INSERT INTO tax_revenue
  (year, quarter, revenue)
VALUES
  ('2020', '1', 3515),
  ('2020', '2', 3678),
  ('2020', '3', 4203),
  ('2020', '4', 3924),
  ('2021', '1', 3102),
  ('2021', '2', 3293),
  ('2021', '3', 3602),
  ('2021', '4', 2901);

Use the following SELECT statement to show all rows of this table:

SELECT * FROM tax_revenue;

 id | year | quarter | revenue
----+------+---------+---------
  1 | 2020 | 1       |    3515
  2 | 2020 | 2       |    3678
  3 | 2020 | 3       |    4203
  4 | 2020 | 4       |    3924
  5 | 2021 | 1       |    3102
  6 | 2021 | 2       |    3293
  7 | 2021 | 3       |    3602
  8 | 2021 | 4       |    2901
(8 rows)

Example 1

The following statement uses a PostgreSQL lead() function to add a last_quarter_revenue column to compare current quarter and previous quarter earnings:

SELECT *,
  lead(revenue, 1) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

 id | year | quarter | revenue | last_quarter_revenue
----+------+---------+---------+----------------------
  4 | 2020 | 4       |    3924 |                 4203
  3 | 2020 | 3       |    4203 |                 3678
  2 | 2020 | 2       |    3678 |                 3515
  1 | 2020 | 1       |    3515 |               <null>
  8 | 2021 | 4       |    2901 |                 3602
  7 | 2021 | 3       |    3602 |                 3293
  6 | 2021 | 2       |    3293 |                 3102
  5 | 2021 | 1       |    3102 |               <null>
(8 rows)

Note that the window function in the SQL statement above:

lead(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) last_quarter_revenue

In the OVER clause,

The PARTITION BY year partitions all rows by year.
The ORDER BY quarter DESC sort the rows within each partition in descending order by quarter.
The lead(revenue, 1) returns the revenue (revenue) from the next row (1) for within the partition where the current row is located.

So the value of the last_quarter_revenue column is the revenue of the previous quarter of the current row. And the value of the last_quarter_revenue column in the last row of each partition is null.

You can specify a default value for nulls in the last_quarter_revenue column. The following statement is used 0 as a default value:

SELECT *,
  lead(revenue, 1, 0) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

 id | year | quarter | revenue | last_quarter_revenue
----+------+---------+---------+----------------------
  4 | 2020 | 4       |    3924 |                 4203
  3 | 2020 | 3       |    4203 |                 3678
  2 | 2020 | 2       |    3678 |                 3515
  1 | 2020 | 1       |    3515 |                    0
  8 | 2021 | 4       |    2901 |                 3602
  7 | 2021 | 3       |    3602 |                 3293
  6 | 2021 | 2       |    3293 |                 3102
  5 | 2021 | 1       |    3102 |                    0
(8 rows)

lead() Syntax