LAG

This section contains reference documentation for the LAG function.

Returns the value from a preceding row in the same result set, based on a specified physical offset. It can be used to compare values in the current row with values in a previous row.

Signature

LAG(any expression [, bigint offset [, any default]])

Arguments

  • expression: The column or calculation from which the value is to be returned.

  • offset: The number of rows before the current row from which to retrieve the value. The default is 1 if not specified.

  • default: The value to return if the offset goes beyond the scope of the window. If not specified, NULL is returned.

Example

This example calculates the difference in sales between the current day and the previous day.

Retrieve the previous payment amount for comparison.

Identify trends by comparing current data with historical data.

Calculate the difference in sales between the current day and the previous day This example shows how to use the LAG function to find the sales difference between consecutive days.

SELECT
    sales_date,
    sales_amount,
    LAG(sales_amount, 1) OVER (ORDER BY sales_date) AS previous_day_sales,
    sales_amount - LAG(sales_amount, 1) OVER (ORDER BY sales_date) AS difference
FROM
    daily_sales;

Output:

sales_date
sales_amount
previous_day_sales
difference

2023-02-14

200

NULL

NULL

2023-02-15

180

200

-20

2023-02-16

220

180

40

Retrieve the previous payment amount for comparison This query retrieves the last payment amount for each payment to see if the amount is increasing or decreasing.

SELECT
    payment_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY payment_date) AS previous_amount
FROM
    payment;

Output:

payment_date
amount
previous_amount

2023-02-14 21:21:59.996577

2.99

NULL

2023-02-14 21:23:39.996577

4.99

2.99

2023-02-14 21:29:00.996577

4.99

4.99

Identify trends by comparing current data with historical data Use the LAG function to compare the current month's data with the same month from the previous year to identify trends or significant changes.

SELECT
    month,
    year,
    data_value,
    LAG(data_value, 12) OVER (ORDER BY year, month) AS previous_year_data
FROM
    monthly_data;

Output:

month
year
data_value
previous_year_data

1

2023

150

NULL

1

2024

170

150

Use with CTE:

WITH tmp AS (
  select count(*) as num_trips,
    DaysSinceEpoch
  from airlineStats
  GROUP BY DaysSinceEpoch
)

SELECT DaysSinceEpoch,
  num_trips,
  LAG(num_trips, 2) OVER (
    ORDER BY DaysSinceEpoch
  ) AS previous_num_trips,
  num_trips - LAG(num_trips, 2) OVER (
    ORDER BY DaysSinceEpoch
  ) AS difference
FROM tmp;