What is LAG and LEAD?
SQLLevel: IntermediateWindow Functions
What is LAG and LEAD?
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
LAG accesses a value from a previous row in the window. LEAD accesses a value from a subsequent row.
SELECT
order_date,
total_sales,
LAG(total_sales, 1) OVER (ORDER BY order_date) AS prev_day_sales,
LEAD(total_sales, 1) OVER (ORDER BY order_date) AS next_day_sales,
total_sales - LAG(total_sales, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM daily_sales;
Both accept an offset (how many rows back/forward, default 1) and a default value for when there's no previous/next row.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT