SQLRepGuidesPlaygroundLearning path

SQL LAG and LEAD: compare a row with the previous or next one

LAG(column) reads the value from the previous row in the window's order; LEAD reads the next one. They're how SQL compares a row with its neighbor.

Syntax

LAG(column, offset, default) OVER (PARTITION BY ... ORDER BY ...). The offset defaults to 1. The first row of each partition has no previous row, so LAG returns NULL there (or your default).

SELECT customer_id, order_date,
  LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order
FROM orders;

Typical uses

Change since last period (amount - LAG(amount)), gaps between events (date - LAG(date)), and consecutive days (a gap of exactly 1). Use LEAD for 'time until next'.

Try it

This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.

try it

Starting Postgres in your browser…

Real Postgres in your browser. Edit the query and run it, or open it in the full playground.

Common mistakes

  • Missing PARTITION BY, so one customer's first order is compared with another customer's last.
  • Filtering on LAG in WHERE instead of an outer query.
  • Using LAG when the question asks about the next row (LEAD).

Practice drills

Reading it isn't enough. Solve these and it'll still be there in an interview.