PostgreSQL LAG Window Function

Summary: In this tutorial, you’ll learn how to use the PostgreSQL LAG window function to compare the current row’s value with the previous row.

Getting Started with the PostgreSQL LAG Window Function #

In PostgreSQL, the LAG() function allows you to access data of a previous row from the current row within the same partition.

You often use the LAG() function to compare the current row’s value with the previous row, such as sales vs. previous year.

Here’s the syntax of the PostgreSQL LAG window function:

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

In this syntax

value: A column or expression that you want to return the value of the previous row.
offset: Specifies the number of rows back from the current row you want to retrieve the value. The offset defaults to 1, which accesses the previous row’s value.
default: This is the default value if the previous row does not exist. If you don’t specify a default value, the function returns NULL if the current row is the first row in the partition, which has no previous row.
PARTITION BY: Divides the result set into partitions by the partition_expression. If you omit the PARTITION BY clause, the LAG function treats the whole result set as a single partition.
ORDER BY: Determines the order of the rows within each partition.

PostgreSQL LAG Window Function Examples #

Let’s explore some examples of using the LAG function with the sales table and sales_by_years view:

The sales table:

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

Try it

The sales_by_years view:

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

Try it

Using PostgreSQL LAG to Compare Yearly Sales #

The following SELECT statement uses the LAG() function to compare the sales amount of each year with the previous year in the sales_by_years view:

SELECT
  year,
  amount,
  LAG(amount) OVER (
    ORDER BY year
  ) AS previous_year_amount
FROM
  sales_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year |  amount   | previous_year_amount
------+-----------+----------------------
 2019 | 513700.00 |                 NULL
 2020 | 490000.00 |            513700.00
 2021 | 512000.00 |            490000.00
 2022 | 483200.00 |            512000.00
 2023 | 491000.00 |            483200.00Code language: plaintext (plaintext)

How it works:

The ORDER BY clause sorts the rows of the result set by year in ascending order.
The LAG(amount) function accesses the amount of the previous year. Note that offset defaults to 1. Since the first row (2019) has no previous row, the LAG() function returns NULL.

Calculating Sales Change Percentage vs. Previous Year #

The following SELECT statement uses the LAG window function to calculate the percentage change in sales compared to the previous year:

SELECT 
    year,
    amount,
    LAG(amount) OVER (ORDER BY year) AS previous_year_amount,
    ROUND((amount - LAG(amount) OVER (ORDER BY year)) / LAG(amount) OVER (ORDER BY year) * 100, 2) AS sales_change_percentage
FROM 
    sales_by_years;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

year |  amount   | previous_year_amount | sales_change_percentage
------+-----------+----------------------+-------------------------
 2019 | 513700.00 |                 NULL |                    NULL
 2020 | 490000.00 |            513700.00 |                   -4.61
 2021 | 512000.00 |            490000.00 |                    4.49
 2022 | 483200.00 |            512000.00 |                   -5.63
 2023 | 491000.00 |            483200.00 |                    1.61Code language: plaintext (plaintext)

How it works:

LAG(amount) OVER (ORDER BY year): Returns the sales amount from the previous year.
(amount - LAG(amount) OVER (ORDER BY year)) / LAG(amount) OVER (ORDER BY year) * 100: Calculates the percentage change in sales compared to the previous year.
ROUND(): Rounds the percentage change to two decimal places.

Comparing Sales by Products #

The following SELECT statement uses the LAG() window function to compare the sales of different products year by year:

SELECT
  year,
  product,
  amount,
  LAG(amount) OVER (
    PARTITION BY product
    ORDER BY year
  ) AS previous_year_amount
FROM
  sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 year | product |  amount   | previous_year_amount
------+---------+-----------+----------------------
 2019 | Galaxy  | 296000.00 |                 NULL
 2020 | Galaxy  | 273000.00 |            296000.00
 2021 | Galaxy  | 272000.00 |            273000.00
 2022 | Galaxy  | 258200.00 |            272000.00
 2023 | Galaxy  | 260000.00 |            258200.00
 2019 | iPhone  | 217700.00 |                 NULL
 2020 | iPhone  | 217000.00 |            217700.00
 2021 | iPhone  | 240000.00 |            217000.00
 2022 | iPhone  | 225000.00 |            240000.00
 2023 | iPhone  | 231000.00 |            225000.00Code language: plaintext (plaintext)

How it works:

PARTITION BY product: Divides the rows into partitions based on the product.
ORDER BY year: Orders the rows within each partition by year.
LAG(amount): Applies to each partition (product) and returns the sales amount from the previous year for the same product.

Comparing Sales vs. Previous Year by Product in Percentage #

The following SELECT statement uses the LAG() function to calculate the percentage change in sales for each product compared to the previous year:

SELECT
  year,
  product,
  amount,
  LAG(amount) OVER (
    PARTITION BY product
    ORDER BY year
  ) AS previous_year_amount,
  ROUND(
    (
      amount - LAG(amount) OVER (
        PARTITION BY product
        ORDER BY year
      )
    ) / LAG(amount) OVER (
      PARTITION BY product
      ORDER BY year
    ) * 100,
    2
  ) AS sales_change_percentage
FROM
  sales;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

 year | product |  amount   | previous_year_amount | sales_change_percentage
------+---------+-----------+----------------------+-------------------------
 2019 | Galaxy  | 296000.00 |                 NULL |                    NULL
 2020 | Galaxy  | 273000.00 |            296000.00 |                   -7.77
 2021 | Galaxy  | 272000.00 |            273000.00 |                   -0.37
 2022 | Galaxy  | 258200.00 |            272000.00 |                   -5.07
 2023 | Galaxy  | 260000.00 |            258200.00 |                    0.70
 2019 | iPhone  | 217700.00 |                 NULL |                    NULL
 2020 | iPhone  | 217000.00 |            217700.00 |                   -0.32
 2021 | iPhone  | 240000.00 |            217000.00 |                   10.60
 2022 | iPhone  | 225000.00 |            240000.00 |                   -6.25
 2023 | iPhone  | 231000.00 |            225000.00 |                    2.67Code language: plaintext (plaintext)

How it works:

PARTITION BY product: Divides the result set into partitions based on the product.
ORDER BY year: Sorts the rows within each partition by year in ascending order.
LAG(amount): Retrieves the sales amount from the previous year for the same product.
(amount - LAG(amount) OVER (PARTITION BY product ORDER BY year)) / LAG(amount) OVER (PARTITION BY product ORDER BY year) * 100: Calculates the percentage change in sales for the same product compared to the previous year.
ROUND: Rounds the percentage change to two decimal places.

Summary #

Use the PostgreSQL LAG() window function to access data from a previous row in the same partition.

Quiz #

Was this tutorial helpful ?