Skip to content
Quantoros

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

Related questions