LAG & LEAD
LAG accesses a value from a previous row within the partition. LEAD accesses a value from a following row. Both take an offset (default 1) and an optional default value when the row doesn't exist.
LAG is looking in the rearview mirror (previous row). LEAD is looking out the windshield (next row). Without ORDER BY, you don't know which direction you're driving.
Usage: LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY hire_date) — returns previous employee's salary, defaulting to 0 for the first employee. LEAD is the forward equivalent. Common use cases: period-over-period comparisons (current month vs last month), consecutive event analysis (time between events), detecting state changes (previous status ≠ current status). Without a default, LAG/LEAD returns NULL when no previous/next row exists.
LAG and LEAD are sugar over FIRST_VALUE/LAST_VALUE with specific frame specs, but they're cleaner and the planner optimizes them directly. For multi-step lookback, use LAG with offset > 1: LAG(value, 3) to look 3 rows back. Detecting consecutive sequences: rows where the row_number minus the value rank forms a constant island. LAG is commonly used to compute deltas: value - LAG(value) OVER (...) AS change. For time-series gap detection: LEAD(event_time) - event_time AS gap, then filter for gaps exceeding a threshold.
LAG and LEAD are essential for time-series analysis — comparing current vs previous period, computing deltas, and finding state transitions. Pattern: SELECT date, revenue, LAG(revenue) OVER (ORDER BY date) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY date) AS change. Always specify a default value to avoid NULLs for boundary rows.
Forgetting ORDER BY in the window spec for LAG/LEAD. Without ORDER BY, 'previous' and 'next' are undefined — the result is non-deterministic. Window functions that reference row position always require ORDER BY.