What do LAG and LEAD do in SQL?
SHORT ANSWER
LAG returns a value from a previous row and LEAD from a following row, within the window order. They compare a row to its neighbour without a self join.
Explain it simply
Looking at a daily petrol price list, LAG lets you glance at the previous line to see yesterday's price, and LEAD lets you peek at the next line. Neither needs you to build a second copy of the table.
Example
Table fuel(day, price) with prices 100, 102, 101 on days 1 to 3.
sql
SELECT day, price,
LAG(price) OVER (ORDER BY day) AS prev_price,
LEAD(price) OVER (ORDER BY day) AS next_price,
price - LAG(price) OVER (ORDER BY day) AS change
FROM fuel;| day | price | prev_price | next_price | change |
|---|---|---|---|---|
| 1 | 100 | NULL | 102 | NULL |
| 2 | 102 | 100 | 101 | 2 |
| 3 | 101 | 102 | NULL | -1 |
- The first row has no previous row, so LAG gives NULL. LEAD on the last row is also NULL.
- Optional arguments:
LAG(price, 2, 0)looks two rows back and returns 0 instead of NULL when there is no such row. - Add
PARTITION BYto restart per group, for example per product.
What to say in the interview
"LAG and LEAD fetch a value from a row at a given offset before or after the current row in the window order. They replace self joins for day-over-day or month-over-month comparisons."
Common mistakes
- Omitting ORDER BY in OVER, which makes the previous row meaningless.