SQL Academy · Lesson

Lag/Lead with Frame Windows

Combine LAG/LEAD with frame windows to compute period deltas and detect gaps in time series.

LAG/LEAD Recap

LAG(col, n) returns the value n rows before the current row in the window. LEAD(col, n) looks ahead.

SELECT day, revenue,
       LAG(revenue) OVER (ORDER BY day) AS prev_day_revenue
FROM daily_revenue;

The third argument is a default when no neighbour exists:

LAG(revenue, 1, 0) OVER (ORDER BY day)
-- Returns 0 instead of NULL for the very first row.