SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons

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

# Pull the previous value in order.

SELECT day,
       revenue,
       LEAD(revenue) OVER (ORDER BY day) AS next_revenue
FROM daily_revenue;

# Preview the next value in sequence.

SELECT day,
       revenue,
       LAG(revenue, 1, 0) OVER (ORDER BY day) AS prev_revenue_or_zero
FROM daily_revenue;

# Avoid NULL on the first row.

SELECT month,
       signups,
       LAG(signups, 2) OVER (ORDER BY month) AS signups_2_months_ago
FROM monthly_signups;

# Custom offset with LAG.

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

# Subtract the previous day's value.

SELECT day,
       revenue,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
         / NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
         2
       ) AS pct_change
FROM daily_revenue;

# Compute relative change versus previous row.

SELECT user_id,
       event_time,
       event_time - LAG(event_time) OVER (
         PARTITION BY user_id
         ORDER BY event_time
       ) AS time_since_prev
FROM user_events;

# Measure time since the prior event.

SELECT customer_id,
       billing_month,
       status,
       LEAD(status) OVER (
         PARTITION BY customer_id
         ORDER BY billing_month
       ) AS next_status
FROM subscription_history;

# Use LEAD for forward state checks.