Home » PostgreSQL Window Functions » PostgreSQL LEAD Window Function

PostgreSQL LEAD Window Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL LEAD() window function to access an offset row after the current row within the partition.

LEAD() Window Function Overview #

In PostgreSQL, the LEAD() is a window function that accesses a row after the current row at an offset within a partition.

Here’s the basic syntax of the LEAD() window function:

LEAD(expression [,offset [,default]])
OVER (    
    [PARTITION BY partition_expression]    
    [ORDER BY sort_expression]
)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

  • expression: returns the value of the row after the current row at an offset within a partition.
  • offset: a positive integer that indicates the number of rows after the current row.
  • default: the value to return if the row at the offset from the current row does not exist. If you don’t provide a default and the row does not exist, the LEAD() function returns NULL.
  • PARTITION BY: divides the rows by the partition_expression into partitions. If you omit the PARTITION BY clause, the function will treat the whole result set as a single partition.
  • ORDER BY: determines the order of rows in each partition.

Examples of PostgreSQL LEAD Window Function #

Let’s explore examples of using the LEAD() window function with the sales_forecasts table:

SELECT * FROM sales_forecasts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

And the sales_forecast_by_years view:

SELECT * FROM sales_forecast_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Comparing the Sales Forecast for the Current and the Following Year #

The following SELECT statement uses the LEAD() function to compare the sales forecasts for the current and following years:

SELECT
  year,
  amount,
  LEAD(amount, 1) OVER (
   ORDER BY year
  ) following_year_forecast
FROM
  sales_forecast_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year |  amount   | following_year_forecast
------+-----------+-------------------------
 2025 | 500000.00 |               525000.00
 2026 | 525000.00 |               555000.00
 2027 | 555000.00 |               585000.00
 2028 | 585000.00 |               615000.00
 2029 | 615000.00 |               645000.00
 2030 | 645000.00 |                    NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

  • The ORDER BY clause sorts the rows from the sales_forecast_by_years view by year from low to high.
  • For each row, the LEAD function looks at the amount value of the next row, which is the following year, and returns it as the following year’s forecast.

The result set includes the year, amount, and following_year_forecast columns. The following_year_forecast displays the sales forecast for the following year.

Calculating the Sales Forecast Change in Percentage #

The following SELECT statement uses the LEAD() function to calculate the sales forecast change in percentage between two subsequent years:

SELECT
  year,
  amount,
  LEAD(amount, 1) OVER (
   ORDER BY year
  ) following_year_forecast,
ROUND((LEAD(amount, 1) OVER (
   ORDER BY year
  ) - amount) * 100 / amount, 2) change
FROM
  sales_forecast_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year |  amount   | following_year_forecast | change
------+-----------+-------------------------+--------
 2025 | 500000.00 |               525000.00 |   5.00
 2026 | 525000.00 |               555000.00 |   5.71
 2027 | 555000.00 |               585000.00 |   5.41
 2028 | 585000.00 |               615000.00 |   5.13
 2029 | 615000.00 |               645000.00 |   4.88
 2030 | 645000.00 |                    NULL |   NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example:

  • For 2025, the following year’s forecast is 525,000, and the change is 5.00 %.
  • For 2026, the following year’s forecast is 555,000, and the change is 5.71%.
  • This pattern continues until the last year, 2030, when the following year’s forecast is NULL because there is no subsequent year.

In this example, we add a new column change to reflect the sales change in percentage. We use the following formula to calculate the sales forecast year over year in percentage:

change = ((following year) - (current year)) * 100 / (current year)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The ROUND function rounds the change to a number with two numbers after the decimal point.

Comparing the Sales Forecast by Product #

The following SELECT statement uses the LEAD() function to compare the sales for each product:

SELECT
  year,
  product,
  amount,
  LEAD(amount, 1) OVER (
   PARTITION BY product
   ORDER BY year
  ) following_year_forecast
FROM
  sales_forecasts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year | product |  amount   | following_year_forecast
------+---------+-----------+-------------------------
 2025 | Galaxy  | 240000.00 |               250000.00
 2026 | Galaxy  | 250000.00 |               265000.00
 2027 | Galaxy  | 265000.00 |               280000.00
 2028 | Galaxy  | 280000.00 |               295000.00
 2029 | Galaxy  | 295000.00 |               310000.00
 2030 | Galaxy  | 310000.00 |                    NULL
 2025 | iPhone  | 260000.00 |               275000.00
 2026 | iPhone  | 275000.00 |               290000.00
 2027 | iPhone  | 290000.00 |               305000.00
 2028 | iPhone  | 305000.00 |               320000.00
 2029 | iPhone  | 320000.00 |               335000.00
 2030 | iPhone  | 335000.00 |                    NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works:

  • The PARTITION BY clause divides the result set into partitions by product.
  • The ORDER BY clause sorts the rows in each partition by year.
  • For each row, the LEAD() function looks at the amount value of the next row within the same partition (product) and returns it as the following year’s forecast.

The following SELECT statement uses the LEAD() function to calculate the forecasted sales amount for the following year and the percentage change in sales from the current year to the following year:

SELECT
  year,
  product,
  amount,
  LEAD(amount, 1) OVER (
    PARTITION BY
      product
    ORDER BY
      year
  ) following_year_forecast,
  ROUND(
    (
      LEAD(amount, 1) OVER (
        PARTITION BY
          product
        ORDER BY
          product,
          year
      ) - amount
    ) * 100 / amount,
    2
  ) change
FROM
  sales_forecasts;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year | product |  amount   | following_year_forecast | change
------+---------+-----------+-------------------------+--------
 2025 | Galaxy  | 240000.00 |               250000.00 |   4.17
 2026 | Galaxy  | 250000.00 |               265000.00 |   6.00
 2027 | Galaxy  | 265000.00 |               280000.00 |   5.66
 2028 | Galaxy  | 280000.00 |               295000.00 |   5.36
 2029 | Galaxy  | 295000.00 |               310000.00 |   5.08
 2030 | Galaxy  | 310000.00 |                    NULL |   NULL
 2025 | iPhone  | 260000.00 |               275000.00 |   5.77
 2026 | iPhone  | 275000.00 |               290000.00 |   5.45
 2027 | iPhone  | 290000.00 |               305000.00 |   5.17
 2028 | iPhone  | 305000.00 |               320000.00 |   4.92
 2029 | iPhone  | 320000.00 |               335000.00 |   4.69
 2030 | iPhone  | 335000.00 |                    NULL |   NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

  • Use the PostgreSQL LEAD() window function to access a row after the current row at an offset.

Quiz #

Was this tutorial helpful ?
PostgreSQL LAST_VALUE Window Function
Previously
PostgreSQL LAST_VALUE Window Function
Up Next
PostgreSQL LAG Window Function
PostgreSQL LAG Window Function