Part 95: Value Navigation Functions: LAG() & LEAD()

LAG() accesses data from previous rows, while LEAD() accesses data from subsequent rows, enabling effortless time-series and delta calculations without self-joins.

1. Calculating Day-over-Day Revenue Differences

SELECT 
  sale_date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY sale_date) AS prev_day_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY sale_date) AS daily_difference
FROM daily_sales;

Key Takeaways

• LAG() reads preceding rows; LEAD() reads following rows.
• Eliminates messy self-joins for delta and trend calculations.

In Part 96, we explore Extremity Functions: FIRST_VALUE(), LAST_VALUE() & NTH_VALUE()!