LAG and LEAD — comparing rows to previous or next values

LAG and LEAD Access Neighboring Rows in a Window

LAG returns the value of a column from a previous row in the window. LEAD returns it from a future row. Both are window functions — they don't change the number of rows in the result.

SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month)  AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0)
            OVER (ORDER BY month)            AS month_over_month_change
FROM monthly_revenue
ORDER BY month;

Parameters: Offset and Default

LAG(column, offset, default) — offset defaults to 1 (previous row), default is what to return when there is no previous row (e.g., the first row). Without a default, LAG returns NULL for the first row.

-- Compare each day's signups to 7 days ago
SELECT
  signup_date,
  signups,
  LAG(signups, 7, 0) OVER (ORDER BY signup_date) AS signups_7d_ago
FROM daily_signups;

Practical Applications

Period-over-period comparisons are the most common use: month-over-month revenue, week-over-week active users, day-over-day signups. LEAD is useful for calculating time-to-next-event, such as the gap between a user's first and second order:

SELECT
  customer_id,
  order_date,
  LEAD(order_date) OVER
    (PARTITION BY customer_id ORDER BY order_date) AS next_order_date
FROM orders;