What do LAG and LEAD do, and how would you use them to find day-over-day change?
LAG reads a column from a previous row in the window order and LEAD from a following row. Subtracting LAG from the current value gives the change from the previous row, without a self-join.
Explain it simply
Looking at a cricket scoreboard, you often want to compare this over with the last over. LAG lets a row peek back at the previous row, and LEAD lets it peek forward. The first row has no previous row, so LAG gives NULL, unless you supply a default.
Example
SELECT day, sales,
LAG(sales) OVER (ORDER BY day) AS prev_sales,
sales - LAG(sales) OVER (ORDER BY day) AS change,
LEAD(sales) OVER (ORDER BY day) AS next_sales
FROM daily_sales;
-- day | sales | prev_sales | change | next_sales
-- 2026-10-01 | 1000 | | | 1200
-- 2026-10-02 | 1200 | 1000 | 200 | 900
-- 2026-10-03 | 900 | 1200 | -300 |
-- default of 0 instead of NULL, and jump 2 rows back
SELECT day, LAG(sales, 2, 0) OVER (ORDER BY day) FROM daily_sales;Add PARTITION BY product to compare within each product separately. Before window functions, this required joining a table to itself on day = day - 1, which fails when days are missing.
What to say in the interview
"LAG and LEAD fetch values from a row before or after the current one in the window order. I use LAG for day-over-day or month-over-month change, partition by the entity, and give a default so the first row is not NULL."
Common mistakes
- Forgetting ORDER BY inside OVER, which leaves the order undefined.