Course contentsShow
← Data Science
Lesson 1027 of 2,14526. Advanced SQLFree lesson

Combining LAG and LEAD for Context

Use both functions together to compare current values with both previous and next values simultaneously.

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.