Combining LAG and LEAD for Context
What You'll Learn
You'll learn how to use LAG and LEAD together in a single query to compare a current row's value against both its preceding and following rows simultaneously, giving you complete temporal context.
Understanding Combined Context
When you use LAG and LEAD separately, you see either what came before or what comes next. But many real-world analyses require both directions at once—like determining whether today's sales are locally high (greater than yesterday and tomorrow), or identifying trend reversals where values changed direction.
Think of it like standing on a hiking trail: LAG shows you where you've been, LEAD shows you where you're going next, but using both together lets you understand whether you're at a peak, a valley, or somewhere in between.
Basic Pattern
SELECT
date,
revenue,
LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
LEAD(revenue) OVER (ORDER BY date) AS next_revenue,
revenue - LAG(revenue) OVER (ORDER BY date) AS change_from_prev,
LEAD(revenue) OVER (ORDER BY date) - revenue AS change_to_next
FROM sales;
This query gives you the complete picture: the previous value, the next value, and how the current row compares to both.
Practical Example: Finding Local Peaks
SELECT
date,
temperature,
LAG(temperature) OVER (ORDER BY date) AS prev_temp,
LEAD(temperature) OVER (ORDER BY date) AS next_temp
FROM weather
WHERE temperature > LAG(temperature) OVER (ORDER BY date)
AND temperature > LEAD(temperature) OVER (ORDER BY date);
This identifies days where temperature was higher than both the day before and the day after—local maximum points.
Key Takeaway: Combining LAG and LEAD in the same query provides bidirectional context, enabling sophisticated pattern detection like finding peaks, valleys, or trend reversals by comparing each row against both neighbors simultaneously.