LAG / LEAD — Previous and Next Row

-- Daily revenue + previous day's revenue + % change
SELECT
  day,
  revenue,
  LAG(revenue)  OVER (ORDER BY day) AS prev_day,
  revenue - LAG(revenue) OVER (ORDER BY day) AS delta,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
          / NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
    1
  ) AS pct_change
FROM   daily_revenue
ORDER  BY day;

-- Time between consecutive events per user
SELECT
  user_id,
  event_at,
  LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_event_at,
  event_at - LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS gap
FROM   events;

-- LEAD with a default value for the last row
SELECT name, score, LEAD(name, 1, '—') OVER (ORDER BY score DESC) AS next_runner_up
FROM   leaderboard;