Compare a row to the previous one (LAG / LEAD)

LAG looks at the previous row, LEAD at the next — perfect for deltas and “compared to last period”. Identical syntax everywhere.

SELECT
  d,
  amount,
  LAG(amount)  OVER (ORDER BY d)        AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY d) AS day_change,
  LEAD(amount) OVER (ORDER BY d)        AS next_amount
FROM sales
ORDER BY d;

Per group, and with a default for the first row (so the change isn’t NULL):

SELECT
  customer_id, d, amount,
  amount - LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY d) AS change_vs_prev
FROM sales;

LAG(amount, 1, 0) = go back 1 row, and use 0 when there’s no previous row. The same optional (offset, default) arguments work in all six dialects.

The first row is NULL. Without the third default argument, LAG/LEAD return NULL at the partition edge, and amount - NULL is NULL. Either supply a default or wrap the subtraction in COALESCE(...).

Works in MySQL 8+, PostgreSQL, SQLite 3.25+, BigQuery, Snowflake and DuckDB.