sql

🔷 SQL: LAG and LEAD — Comparing Rows Across Time

LAG(column, N) = look N rows back. LEAD(column, N) = look N rows forward.

-- Month-over-month revenue growth
WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly;

Two gotchas here: - 100.0 forces decimal division (integer 100 would silently round to 0) - NULLIF(x, 0) returns NULL instead of dividing by zero (first month has no LAG)

Practice Questions

Q: Write a query to find users whose spending THIS month is more than double their LAST month's spending.