LEAD Function: Accessing Next Row Values
What you'll learn: How to use the LEAD function to look ahead and retrieve values from rows that come after the current row in your ordered dataset.
What is LEAD?
If LAG lets you look backward at previous rows, LEAD does the opposite—it lets you peek forward into upcoming rows. Think of it like reading a book: while LAG remembers what you just read on the previous page, LEAD gives you a sneak preview of what's coming next.
The LEAD Syntax
LEAD(column, offset, default) OVER (ORDER BY sort_column)
- column: The value you want to retrieve from a future row
- offset: How many rows ahead to look (default is 1)
- default: What to return if there's no next row (like the last row in your dataset)
How It Works
SELECT
sale_date,
revenue,
LEAD(revenue, 1, 0) OVER (ORDER BY sale_date) AS next_day_revenue
FROM daily_sales;
This query shows today's revenue alongside tomorrow's revenue. For the last row (most recent date), there's no "next" row, so it returns 0 (your default value).
Common Use Cases
Compare current values to future ones:
SELECT
month,
sales,
LEAD(sales) OVER (ORDER BY month) AS next_month_sales,
sales - LEAD(sales) OVER (ORDER BY month) AS sales_drop
FROM monthly_data;
Key Takeaway: LEAD retrieves values from rows after the current one in ordered data, perfect for forward-looking comparisons, trend analysis, and identifying upcoming changes in sequential datasets.