Select Distinct Logo Clear Background

Business Analytics Blog

LEAD and LAG Functions in SQL

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 functions are essential tools for data analysis with SQL. 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 Function 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

Simple list of dates in SQL

 

 

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

Lead function in SQL

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


SQL Lag and LEAD functions

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

Finding the next week using LEAD in SQL

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

Find the previous days closing stock using LAG in SQL

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

 

Watch our You Tube Video

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert