SQL LEAD Function Explained

The LEAD() function is a window function in SQL that allows you to access data from a subsequent row (a following row) in the same result set without using a self-join. It is the counterpart to the LAG() function.

Syntax

LEAD(return_value , offset [, default])
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• return_value: The value to be returned from the following row.
• offset: The number of rows forward from the current row. The default is 1.
• default: The value to return if the offset goes beyond the scope of the partition. The default is NULL.

Example 1: Forecasting Next Day’s Temperature

Suppose we have a weather table.

We want to show the next day’s temperature alongside the current day.

SELECT 
    Date,
    Temperature,
    LEAD(Temperature) OVER (ORDER BY Date) AS NextDayTemp
FROM weather;

Result:

Example 2: Analyzing Session Durations

If you have a log of user events, LEAD() can help calculate the time until the next event.

SELECT 
    EventID,
    EventTime,
    LEAD(EventTime) OVER (ORDER BY EventTime) - EventTime AS TimeToNextEvent
FROM user_logs;

LEAD vs LAG

| Function | Direction | Use Case | | men | men | men | | LAG() | Backward | Comparing with past data (e.g., yesterday’s sales) | | LEAD() | Forward | Comparing with future data (e.g., next scheduled appointment) |

Key Takeaways

• Use LEAD() to “look ahead” in your result set.
• It is extremely useful for calculating durations, gaps, or preparing data for time-series forecasting models.
• Like LAG(), the ORDER BY clause is critical for defining the sequence.