SQL Window Functions - Understanding LEAD and LAG

Introduction

LEAD and LAG functions let you look at values from other rows in your result set:

• LAG looks at previous rows
• LEAD looks at following rows

Why LEAD and LAG Matter?

They’re useful when you need to:

• Compare current value with previous/next value
• Find changes between consecutive rows
• Look at data before or after the current row
• Calculate differences between rows

Main Content: Understanding LEAD and LAG

Let’s use this simple table:

-- Create a simple table
CREATE TABLE daily_temperatures (
    reading_date DATE,
    temperature INT
);

-- Insert sample data
INSERT INTO daily_temperatures VALUES
('2024-01-01', 20),
('2024-01-02', 22),
('2024-01-03', 19),
('2024-01-04', 23),
('2024-01-05', 21),
('2024-01-06', 20);

Example 1: Basic LEAD and LAG

Let’s look at current, previous, and next day temperatures:

SELECT 
    reading_date,
    temperature as current_temp,
    LAG(temperature) OVER (ORDER BY reading_date) as previous_temp,
    LEAD(temperature) OVER (ORDER BY reading_date) as next_temp
FROM daily_temperatures;

This query shows:

1. Current day’s temperature
2. Previous day’s temperature (LAG)
3. Next day’s temperature (LEAD)

Results:

reading_date | current_temp | previous_temp | next_temp
-------------|--------------|---------------|----------
2024-01-01   | 20          | NULL          | 22
2024-01-02   | 22          | 20            | 19
2024-01-03   | 19          | 22            | 23
2024-01-04   | 23          | 19            | 21
2024-01-05   | 21          | 23            | 20
2024-01-06   | 20          | 21            | NULL

Notice:

• First row has no previous temperature (NULL)
• Last row has no next temperature (NULL)

Example 2: Calculating Changes

Let’s calculate temperature changes between days:

SELECT 
    reading_date,
    temperature,
    temperature - LAG(temperature) OVER (ORDER BY reading_date) as temp_change
FROM daily_temperatures;

This query:

1. Shows each day’s temperature
2. Calculates difference from previous day
3. Shows temperature changes

Results:

reading_date | temperature | temp_change
-------------|-------------|------------
2024-01-01   | 20         | NULL
2024-01-02   | 22         | 2
2024-01-03   | 19         | -3
2024-01-04   | 23         | 4
2024-01-05   | 21         | -2
2024-01-06   | 20         | -1

Example 3: Using Offset Values

Let’s look two days ahead and behind:

SELECT 
    reading_date,
    temperature as current_temp,
    LAG(temperature, 2) OVER (ORDER BY reading_date) as two_days_ago,
    LEAD(temperature, 2) OVER (ORDER BY reading_date) as two_days_ahead
FROM daily_temperatures;

This query:

1. Shows current temperature
2. Shows temperature from 2 days ago
3. Shows temperature 2 days ahead

Results:

reading_date | current_temp | two_days_ago | two_days_ahead
-------------|--------------|--------------|---------------
2024-01-01   | 20          | NULL         | 19
2024-01-02   | 22          | NULL         | 23
2024-01-03   | 19          | 20           | 21
2024-01-04   | 23          | 22           | 20
2024-01-05   | 21          | 19           | NULL
2024-01-06   | 20          | 23           | NULL

Best Practices

1. Ordering

   • Always include ORDER BY
   • Use date/time for time-series data
   • Ensure ordering is clear and logical

2. NULL Handling

   • Remember first/last rows will have NULLs
   • Use COALESCE for default values
   • Consider business rules for NULL handling

3. Offset Values

   • Start with offset of 1 (default)
   • Use larger offsets carefully
   • Consider data volume when using large offsets

4. Common Uses

   • Time series analysis
   • Change detection
   • Trend analysis
   • Sequential comparisons

5. Things to Remember

   • LAG looks backward
   • LEAD looks forward
   • Both return NULL for out-of-range rows
   • Order matters!

Remember:

• LAG gets previous values
• LEAD gets next values
• Both need ORDER BY
• First/last rows will have NULLs

Common Mistakes to Avoid:

1. Forgetting ORDER BY
2. Not handling NULL values
3. Using wrong offset numbers
4. Incorrect ordering for time-series data

Happy querying! 🚀