Recipe

Previous row value (LAG / LEAD)

Short answer: LAG(value) OVER (PARTITION BY group ORDER BY time_col) returns the value from the previous row within the partition. LEAD returns the value from the next row. Both take an optional offset (LAG(value, 2) for two rows back) and a default value (LAG(value, 1, 0) for "use 0 instead of NULL when no previous row exists"). Use these for period-over-period deltas, change detection, and gap-filling.

Canonical SQL #

sql

-- Previous and next values within a partition.
SELECT
  user_id,
  order_date,
  total,
  LAG(total)  OVER (PARTITION BY user_id ORDER BY order_date) AS prev_order_total,
  LEAD(total) OVER (PARTITION BY user_id ORDER BY order_date) AS next_order_total
FROM   orders;

-- With offset and default.
SELECT
  user_id,
  order_date,
  total,
  LAG(total, 1, 0) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_total_or_zero,
  LAG(total, 7)    OVER (PARTITION BY user_id ORDER BY order_date) AS total_7_orders_ago
FROM   orders;

Example #

Loading SQL editor...

Common patterns #

sql

-- Detect status changes.
SELECT
  user_id,
  changed_at,
  status,
  LAG(status) OVER (PARTITION BY user_id ORDER BY changed_at) AS prev_status,
  status <> LAG(status) OVER (PARTITION BY user_id ORDER BY changed_at) AS is_change
FROM user_status_log;

-- Time between consecutive events.
SELECT
  user_id,
  occurred_at,
  occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS gap
FROM events;

-- "Has the user been inactive for >30 days between sessions?" — session boundary.
SELECT
  user_id,
  occurred_at,
  CASE
    WHEN occurred_at - LAG(occurred_at) OVER (
      PARTITION BY user_id ORDER BY occurred_at
    ) > INTERVAL '30 days' OR LAG(occurred_at) OVER (
      PARTITION BY user_id ORDER BY occurred_at
    ) IS NULL
    THEN 1 ELSE 0
  END AS is_new_session
FROM events;

-- Year-over-year comparison (assuming a daily metric).
SELECT
  day,
  revenue,
  LAG(revenue, 365) OVER (ORDER BY day) AS revenue_yoy_naive
FROM daily_revenue;

Dialect notes #

LAG / LEAD are ANSI standard and work on Postgres, MySQL (8.0+), SQL Server, Oracle, Snowflake, BigQuery, DuckDB, Redshift, SQLite (3.25+).
IGNORE NULLS: clause that skips NULL values when looking back. Supported on Snowflake, Oracle, Redshift, BigQuery, DuckDB. Postgres does not support it natively — emulate with array_remove + array indexing, or with a non-window query.
FIRST_VALUE / LAST_VALUE: related window functions. FIRST_VALUE(x) OVER (PARTITION BY g ORDER BY t) returns the first x in the partition; LAST_VALUE returns the last (mind the frame — see below).
LAST_VALUE frame trap: with the default frame (UNBOUNDED PRECEDING AND CURRENT ROW), LAST_VALUE returns the current row's value, not the partition's last. To get the actual last: LAST_VALUE(x) OVER (PARTITION BY g ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING). The fix is verbose; many people switch to FIRST_VALUE with a reversed ORDER BY instead.

Tip

LAG is the building block for almost every "change over time" calculation. Year-over-year, day-over-day, time-between-events, status-changed-from-X-to-Y — all of them. The pattern: LAG(value) OVER (PARTITION BY entity ORDER BY time), then arithmetic against the current row. Combine with running total and you've got the foundation of most time-series analytics.