LEAD and LAG Functions in SQL

A Guide to Lead and Lag Functions in SQL for Analysing and Optimizing Data

Introduction: An Overview of Lead & Lag Functions and Their Benefits in SQL

Lead and Lag in SQL are essential functions for data analysis. These window functions allow users to compare and analyse data points between rows in a specified window. By using Lead and Lag functions, users can identify trends, correlations, and outliers in their data sets more easily than ever before.

Lead and Lag functions are especially useful when analysing time-series data that is stored in SQL databases. With these powerful tools, users can quickly identify changes or patterns over time by comparing values from different rows within a specified window. The ability to quickly identify lead and lag relationships in your data can be extremely beneficial for businesses that need to make decisions based on up-to-date information.

How to Use the Lead in SQL to Compare Rows Across a Table

In this example we will use a date table as its easy to follow and the results are plain and simple to understand

We begin by selecting a list of dates from a date dimension table

select [FullDateUK]
from [SelectDistinctTIPS].[dbo].[DimDate]
where DateKey between 20231229 and 20240107

This returns a simple list of dates

SQL Lead Function

The next thing to do is to return the day after each date as a column, we do this by using the  LEAD function

LEAD([FullDateUK], 1, 0) over (ORDER BY [datekey]) as nextday

The order by clause is used to determine the sorting order to be applied to the result set

Add this code to SQL and run it to return

How the SQL LEAD function works

LEAD tells SQL to look for the next row

[FullDateUK] is the field we want to return

, 1 is the number of rows to look forward

, 0 is the default if no data is available

over (ORDER BY [datekey]) defines the order to sort the data

SQL LAG Function

The LAG function in SQL works in a similar way to the LEAD function, except that it looks for the previous row

select [FullDateUK], LEAD([FullDateUK], 1, 0) over (ORDER BY [datekey]) as nextday
, LEAD([FullDateUK], 1, 0) over (ORDER BY [datekey]) as nextday
, LAG([FullDateUK], 1, 0) over (ORDER BY [datekey]) as prevday
from [SelectDistinctTIPS].[dbo].[DimDate]
where DateKey between 20231229 and 20240107

Other Examples using the Lead and Lag Function in SQL

If we change the parameter to a 7, we can quickly find the dates a week earlier or later

Use Cases for LEAD and LAG

You wouldn’t use LEAD and LAG functions in SQL to find dates as there are date functions to do that, but it does demonstrate nicely how it works

Typical use cases for this,

Finding the prior week or month sales for a sales dataset,

Forecasting closing stock by carrying forward the previous days closing stock

It can be used wherever you need to find either the previous row and or next row of a dataset, like you might do in Excel

Once you understand the LEAD and LAG functions in SQL, with a little bit of experimentation you can apply this technique to a wide variety of use cases

BigQuery LEAD and LAG Functions

The Syntax for Lag and Lead functions here is the same for both SQL Server and BigQuery

If you are looking for how to use LEAD and LAG in BigQuery you can use this same code

Adding in a partition by clause

You can also optionally add in a partition by clause to add in a greater level of control

For example you could compare the sales on a Monday to the previous Monday by adding in a partition by day of week, this would return the sales for the previous week on the subsequent rows

we will save that one for a future post with examples