SQL LEAD Window Functions Explained with Examples

SQL LEAD Window function syntax, explain syntax meaning, provide multiple usage examples, and use the examples to explain LEAD SQL Window function.

LEAD Window Functions in SQL

It’s easier to explain what LEAD window function is with an example. So, I will

Show you the syntax,
Explain what the syntax mean,
Show you multiple examples, then
Use the examples to explain LEAD SQL Window function.

If you prefer to watch video instead of reading, here's a video covering the exact same topic.

Syntax

SQL LEAD window functions has this syntax. I know this syntax looks ambiguous, but don’t gloss over it yet. Take the time to actually understand what each component of the syntax is doing. If you don’t understand the syntax or explanations below, take heart cause the followed examples will clarify any ambiguity or confusion you may have.

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

LEAD = That’s just the SQL keyword clause to identity the function.

return_value = The column or sql query that you want used as the basis for the LEAD Function.

offset = How many rows from the current row should be returned. Default value is 1.

In SQL, the default offset is 0 which means that query results are returned starting from the 1st row. But, you can choose to return query results starting from the 2nd row, or 3rd row, or 4th row, etc. That’s what offset is and the same concept applies here. I will show examples of this below.

If you want query result returned starting from 2nd row, the set offset = 1, 3rd row? offset = 2. etc.

default = Lead window function returns the “next” row from the current row within the same partition. If there is no value in the “next” row, it returns the value defined in “default”. The default value is NULL. You can specify any string or integer as the default value. I will show example of this below.

PARTITION BY expr1 = Window function utilizes partitions. Partitions is a way to group related data together (not to be confused with group by) by a column. What is defined in expr1 is the column/datapoint to be used for the partition.

When nothing is specified for partition, then the entire dataset is treated like a single partition. You can use multiple columns/expressions in your partition. The data will be partitioned in the order the columns were specified in. Below, I have an example of with/without a partition.

ORDER BY expr1 = Order by is exactly what it sounds like. How should the resulting data be ordered? expr1 here represents the column to be used for ordering the data.

Once again just like partition, you can have multiple columns as part of the order by clause. And again, it will be ordered sequentially in the order the columns were specified in.

There are some window functions that are order-dependent which means that how the data is ordered impacts the results. Order by is essentially required for these window functions.

But, LEAD SQL window function IS NOT order-dependent which means that the order by clause can be omitted and you will still get a result. This is possible because regardless of how the data is ordered, the result is determined by the offset from the current row.

Don’t worry if non of these make sense yet, this syntax and explanations will make more sense once you see the examples below.

How to use SQL LEAD Window Function

These are examples of how to use it.

LEAD Window Function Example 1 - Using all Default Values

Let’s start by looking at an example without specifying any partition, offset, or default parameter values.

This query shows an employee hire date followed by the date the next employee was hired.

SELECT 
    first_name,
    last_name, 
    hire_date, 
    LEAD(hire_date, 1) OVER (
        ORDER BY hire_date
    ) AS hired_next
FROM 
    employees;

LEAD Window Function no partition, offset by 1, and no default example results.

What does it do?: Lead window function basically gives you the value in the next row after the current row from the column of interest. The value it provides depends on the offset value.

Let’s use the example above to explain this.

The query above has no partition, so the entire dataset is used as a single partition. You will see the difference between partition and no partition later on. The query above also has 1 as the offset value.

hire_date is the original column and hired_next is the LEAD window function column.

The value in row 1 hired_next column is the same value as row 2 hire_date column.

Again, row 3 hired_next column is same as row 4 hire_date column, etc

That’s how LEAD Window function works. It gives you the value in the next row depending on the offset value defined. Since the offset value for this query is 1, the “next value” is one row below.

It could also give you value from the next 2/3/4/5/6/… rows depending on what value is specified for the offset.

Let’s look at another example.

LEAD Window Function Example 2 - Offset by 2

This example uses an offset of 2 instead of the default value of 1. As you can see in the highlighted numbers below.

SELECT 
    first_name,
    last_name, 
    hire_date, 
    LEAD(hire_date, 2) OVER (
        ORDER BY hire_date desc
    ) AS hired_next
FROM 
    employees;

LEAD Window Function OFFSET by 2 Example Results

This is the same query as the one above, but now, we are using the offset of 2.

The value in hired_next column row 1 is same as hire_date column row 3.

And once again, the value in hired_next column row 5 is same as hire_date column row 7.

This is a clear example of the offset determines the value.

I think you got it by now, but just in case you haven’t, let’s look at one more example, this time using an offset of 5.

LEAD Window Function Example 3 - Offset by 5

And This one, an offset by 5

SELECT 
    first_name,
    last_name, 
    hire_date, 
    LEAD(hire_date, 5) OVER (
        ORDER BY hire_date desc
    ) AS hired_next
FROM 
    employees;

LEAD Window Function OFFSET by 5 Example Results

Same thing again. Single partition, no defaults specified and the value in hired_next column corresponds to the value in hire_date column 5 rows down.

LEAD Window Function Example 4 - Partitioning Example

So far, we have been using a single partition in our examples. Let’s explore using more than one partition to see what the results look like.

This example partitions by department, uses 1 as the offset, and doesn’t provide any value for “default”.

SELECT 
    first_name,
    last_name, 
    department_name,
    hire_date, 
    LEAD(hire_date) OVER (
        PARTITION by department_name
        ORDER BY hire_date
    ) AS hired_next_date
FROM 
    employees e
INNER JOIN departments d ON 
    d.department_id = e.department_id;

Remember what I said above, when default is not provided, the resulting value is NULL (We will get into this below).

LEAD Window Function - Partition by Department Example Results.

What exactly is going on with the orange & purple boxes?

So, there’s a couple of things going on here.

If you look in the department_name column, everything is organized by department.

This is important to remember; with partitioning, the Function restarts with each partition window.

That means that hired_next_date (Which represents the LEAD Window Function results) is only applied to the Accounting department.

When it gets to Administration department, it starts over.

And when it gets to Executive department, it starts over; Same goes for finance department.

That’s what partitioning does. It breaks things into groups and for each groups, the calculation starts over. That’s what you see in the Orange and Purple boxes.

What’s going on with the green boxes?

If you remember earlier, I mentioned that the LEAD Window Function Clause “default” has a default value of NULL. That’s what’s highlighted in the green boxes.

When the function comes to the end of the partition, there is no value in the “next” row, so it returns whatever is specified in the “default” clause and since we didn’t specify any value for “default”, it returns NULL.

What if you wanted to provide a value for “default” instead of NULL? Let’s discuss that next.

LEAD Window Function Example 5 - Specifying a value for “Default”

Like the subheading suggests, this example below shows you how to specify a value for “default”

SELECT 
    first_name,
    last_name, 
    department_name,
    hire_date, 
    LEAD(hire_date, 1, 'Not Applicable') OVER (
        PARTITION by department_name
        ORDER BY hire_date
    ) AS hired_next_date
FROM 
    employees e
INNER JOIN departments d ON 
    d.department_id = e.department_id;

LEAD Window Function - Replace NULL default Value Example Results.

This query and result is the exact same one as in Example 4, but now, we are replacing the NULL Values with “Not Applicable”.

The replacement doesn’t have to be a string. You can could also specify a number as the default value.

So, learning LEAD Window Function is cool and all, but…how do you actually know when and why to use it.

See, whenever I learn something new, I want to know the applicability of it. Like…this new thing I learned is cool, but WHEN do I actually use it?

Let’s talk about that next.

SQL LEAD Window Function USE CASES.

Now you might be wondering, SQL LEAD window function sounds cool, but when will I ever use it? What use-case will this be an effective solution? Don’t worry, I got you covered 😆

A good use-case for SQL LEAD Window Function is calculating the sum or difference between the current row and the offset row.

Let’s look at an example to clarify this.

This example shows subtracting the 2 hire dates to see number of days between employee hires.

 with hiring as 
 (SELECT 
    first_name,
    last_name, 
    hire_date, 
    LEAD(hire_date) OVER (
        ORDER BY hire_date
    ) AS hired_next
FROM 
    employees)
select *,
    julianday(hired_next) - julianday(hire_date) as hire_diff
    from hiring;

julianday is how sqlite converts date (string-format) into number of days since beginning of the julian period.

LEAD Window Function Use Case Example Results.

As you can see, the column hire_diff shows the resulting difference between when the next employee was hired [represented by hired_next (obtained from LEAD function)] and the hire_date of the current employee.

Let’s use row 1 as an example.

In row 1, hired_next represents the date Jennifer (located in row 2) was hired.

Hire_diff is the number of days between when Steven (Row 1) and Jennifer (Row 2) was hired.

I am sure there are many more use cases for SQL LEAD window function, but I hope this example gets you excited to try LEAD window function.

SQL LEAD Window Functions in a Nutshell

SQL LEAD Window function gives you the value in the “next row”. What the next row is, is determined by the offset value. The default offset value is 1, but you can set it to any number.

When there is no value in the next row, NULL is returned. But, instead of NULL being returned when there is no value, you can specify any string or number to be returned instead. For example, you can return “Not applicable” instead of NULL.

You can also specify how to divide and group the data using partition. When partition is not specified, the entire dataset is treated like a single partition.

-------------------------------------------------------------------------------------------------

If you want to replicate these results on your local computer,

Download SQLite to Local Computer
Download the Employees Database. When you sign up to get access to my FREE technical resources, you will get access to this database as well as other databases.

-------------------------------------------------------------------------------------------------

Follow me on Youtube

Follow me on Linkedin

Follow me on Github

Follow me on Twitter

If you want to contact me, speak to me here

Want me to create a similar technical content for your brand? Contact me here.

Want to get updates everytime I publish new content? Join my FREE Technical Newsletter here

Categories: : SQL, SQL Window Functions