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.
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.
- Consecutive-day loginsLAG() · date arithmeticPro
- Month-over-month growthCTE + LAG · growth ratePro
- Longest login streakGaps and islandsPro
- Days until next orderLEAD() · PARTITION BYPro
- Year-over-year growth by quarterSelf-join on period · YoY %Pro
- Activity streaks as date rangesGaps and islands · duplicate daysPro
- Sessions from raw pageviewsLAG · running SUM · sessionizationPro