LAG, LEAD & Value Functions

While ranking and aggregate window functions are powerful, PostgreSQL also provides functions that let you access values from other rows relative to the current row. These are essential for comparing consecutive records, calculating changes over time, and extracting boundary values.

LAG() -- Look Behind

LAG() accesses a value from a previous row in the result set without needing a self-join.

LAG(column, offset, default) OVER (ORDER BY ...)

• column -- the value to retrieve
• offset -- how many rows back to look (default: 1)
• default -- value to return when there is no previous row (default: NULL)

Basic Usage

SELECT
  sale_date,
  amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount
FROM sales
ORDER BY sale_date;

The first row has NULL for prev_amount because there is no row before it.

Calculating Change Between Rows

This is the most common use case -- computing the difference between consecutive values:

SELECT
  sale_date,
  amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS amount_change
FROM sales
ORDER BY sale_date;

Using a Default Value

Avoid NULL for the first row by providing a default:

SELECT
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount
FROM sales
ORDER BY sale_date;

Now the first row shows 0 instead of NULL.

Looking Back Multiple Rows

The second argument specifies how far back to look:

SELECT
  sale_date,
  amount,
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_1,
  LAG(amount, 2) OVER (ORDER BY sale_date) AS prev_2,
  LAG(amount, 3) OVER (ORDER BY sale_date) AS prev_3
FROM sales
ORDER BY sale_date;

LEAD() -- Look Ahead

LEAD() is the mirror of LAG(). It accesses a value from a subsequent row.

LEAD(column, offset, default) OVER (ORDER BY ...)

SELECT
  sale_date,
  amount,
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount,
  LEAD(amount) OVER (ORDER BY sale_date) - amount AS change_to_next
FROM sales
ORDER BY sale_date;

The last row will have NULL for next_amount since there is no row after it.

LAG and LEAD with PARTITION BY

Combine with PARTITION BY to compare within groups:

SELECT
  region,
  sale_date,
  amount,
  LAG(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS prev_region_amount,
  amount - LAG(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS region_change
FROM sales
ORDER BY region, sale_date;

Now each region's comparison is independent -- the first sale in each region will have NULL for prev_region_amount.

FIRST_VALUE()

FIRST_VALUE() returns the value from the first row in the window frame.

SELECT
  region,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS first_sale_amount
FROM sales
ORDER BY region, sale_date;

Every row within a region shows that region's first sale amount. This is useful for comparing each row against a baseline.

Comparing Against the First Value

SELECT
  region,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS first_amount,
  amount - FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS diff_from_first
FROM sales
ORDER BY region, sale_date;

LAST_VALUE()

LAST_VALUE() returns the value from the last row in the window frame. There is an important caveat here.

-- CAUTION: This may not do what you expect!
SELECT
  region,
  sale_date,
  amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
  ) AS last_amount
FROM sales
ORDER BY region, sale_date;

Because the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, LAST_VALUE() just returns the current row's value. To get the true last value, you must extend the frame:

SELECT
  region,
  sale_date,
  amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_sale_amount
FROM sales
ORDER BY region, sale_date;

Now every row correctly shows the last sale amount for its region.

NTH_VALUE()

NTH_VALUE(column, n) returns the value from the nth row in the window frame.

SELECT
  region,
  sale_date,
  amount,
  NTH_VALUE(amount, 1) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_amount,
  NTH_VALUE(amount, 3) OVER (
    PARTITION BY region
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_amount
FROM sales
ORDER BY region, sale_date;

Like LAST_VALUE(), you typically want the full frame (UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING) when using NTH_VALUE().

Practical Patterns

Month-over-Month Comparison with Orders

SELECT
  customer_name,
  order_date,
  total_price,
  LAG(total_price) OVER (
    PARTITION BY customer_name
    ORDER BY order_date
  ) AS prev_order_total,
  ROUND(
    (total_price - LAG(total_price) OVER (
      PARTITION BY customer_name
      ORDER BY order_date
    )) * 100.0 / NULLIF(LAG(total_price) OVER (
      PARTITION BY customer_name
      ORDER BY order_date
    ), 0),
    2
  ) AS pct_change
FROM orders
ORDER BY customer_name, order_date;

Detecting Gaps in a Sequence

SELECT
  sale_date,
  LAG(sale_date) OVER (ORDER BY sale_date) AS prev_date,
  sale_date - LAG(sale_date) OVER (ORDER BY sale_date) AS days_between
FROM sales
ORDER BY sale_date;

Finding the Difference from the Maximum

SELECT
  region,
  sale_date,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS max_in_region,
  amount - FIRST_VALUE(amount) OVER (
    PARTITION BY region
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS diff_from_max
FROM sales
ORDER BY region, amount DESC;

Try It Yourself

1. Use LAG() to show each sale alongside the previous sale's amount, ordered by sale_date. Calculate the change.

2. Use LEAD() to preview the next sale's amount for each region. Which sales have the biggest jump to the next sale?

3. Use FIRST_VALUE() to compare every sale in each region against that region's first sale. Which region has the biggest growth from its first sale?

4. Use LAST_VALUE() with the correct frame clause to show the most recent sale amount per region alongside each row.