LAG, LEAD, FIRST_VALUE, LAST_VALUE

Read a value from a previous, next, first or last row of the window without a self-join.

Syntax

lag(expr [, offset [, default]])  OVER (PARTITION BY ... ORDER BY ...)
lead(expr [, offset [, default]]) OVER (...)
first_value(expr) OVER (...)
last_value(expr)  OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
nth_value(expr, n) OVER (...)

lag(x) returns x from the previous row in the partition's order; lead(x) from the next row. offset defaults to 1. Where there is no such row, the result is default (NULL if not given).
Typical uses: change vs previous period, days between consecutive orders, detecting status transitions.
first_value(x) returns x from the first row of the frame. With the default frame this is the partition's first row, as expected.
last_value(x) with the default frame returns the current row (or its last peer), because the frame ends at the current row. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use first_value with the order reversed.
lag/lead ignore the frame; they use the partition and its order.
NULL: if the previous row's value is NULL, lag returns NULL (PostgreSQL has no IGNORE NULLS).

Examples

Month-over-month change shop

WITH monthly AS (
  SELECT date_trunc('month', ordered_at)::date AS month, count(*) AS orders
  FROM orders
  WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'
  GROUP BY month
)
SELECT month, orders,
       lag(orders) OVER (ORDER BY month) AS prev_month,
       orders - lag(orders) OVER (ORDER BY month) AS change,
       round(100.0 * (orders - lag(orders) OVER (ORDER BY month)) / lag(orders) OVER (ORDER BY month), 1) AS pct_change
FROM monthly
ORDER BY month;

January has no previous month, so its lag columns are NULL.

Days since the customer's previous order starter

SELECT customer_id, order_id, order_date,
       order_date - lag(order_date) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS days_since_prev
FROM orders
ORDER BY customer_id, order_date, order_id;

PARTITION BY restarts the comparison for each customer; each customer's first order shows NULL.

First and last salary per employee people

SELECT DISTINCT employee_id,
       first_value(base_salary) OVER w AS first_salary,
       last_value(base_salary) OVER w AS latest_salary
FROM salary_history
WINDOW w AS (PARTITION BY employee_id ORDER BY effective_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY employee_id
LIMIT 10;

The explicit full frame makes last_value see the whole partition.

Pitfalls

last_value with the default frame (returns the current row's value).
Forgetting PARTITION BY, so the first row of one customer compares with the last row of another.
Using lag on data with missing periods: the 'previous row' is not necessarily the previous month. Build a date spine first.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

Oracle: Supports IGNORE NULLS in LAG/LEAD/FIRST_VALUE/LAST_VALUE.
Snowflake: Supports IGNORE NULLS.
BigQuery: FIRST_VALUE / LAST_VALUE / NTH_VALUE support IGNORE NULLS.
SQL Server: IGNORE NULLS supported since SQL Server 2022.