LEAD and LAG Window Functions

Access previous and next row values in the same result set using LEAD and LAG.

Overview

LAG(col, n, default) returns the value from n rows before the current row. LEAD(col, n, default) returns the value from n rows ahead. Both are window functions and require OVER (ORDER BY ...) to define row order. They are essential for time-series analysis: computing month-over-month changes, comparing with the previous day, or finding consecutive patterns. The optional default parameter (third argument) is returned when there is no previous/next row (e.g., the first row has no LAG value).

Key Points

•LAG(col) OVER (ORDER BY ts) previous row's value
•LEAD(col) OVER (ORDER BY ts) next row's value
•LAG(col, 2) value 2 rows back; LEAD(col, 2) 2 rows ahead
•Third arg is default: LAG(val, 1, 0) returns 0 for first row
•Commonly used for period-over-period comparison (MoM, YoY, DoD)

Examples

Month-over-month revenue comparison

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)           AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month)  AS month_change,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
  ) AS pct_change
FROM monthly_revenue
ORDER BY month;

Exercises

Write SQL to solve each exercise. Your progress is saved automatically.

Loading editor…

Completed: 0 / 5 exercises

LEAD and LAG Window Functions

Overview

Key Points

Examples

Exercises

Knowledge Check

What does LAG(col, 1, 0) return for the first row in the partition?