SQL LAG and LEAD Window Functions: Explained

The SQL Server LAG and LEAD functions are simple window functions you should know to help you gather meaningful information from your data.

Window functions are considered an advanced tool when querying data in SQL Server. If you’re trying to learn about window functions, congratulations, you’re moving up!

The LAG and LEAD functions are two of four offset window functions we have available to us in Microsoft SQL Server. You should understand all the offset window functions if you want to be a great database developer. Check out the full tutorial on offset window functions to learn more:

SQL Server Offset Window Functions: FIRST_VALUE, LAST_VALUE, LAG, LEAD

In this very brief tutorial, we’re going to discuss what the LAG and LEAD functions are and how to use them.

Also, make sure you download your FREE Ebook:

FREE Ebook on SQL Server Window Functions!

This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. This EBook will definitely be a great resource for you to reference throughout your career as a data professional. Get it today!

We’ll discuss these topics:

1. What is a window function?
2. What are the LAG and LEAD window functions?
3. Setting up some data
4. Example of using LAG
5. Example of using LEAD
6. Tips, tricks, and links

Let’s get into it.

1. What is a window function?

Before we dive into the LAG and LEAD functions, we need to understand what a window function is in the first place.

A window function can be used to combine aggregate/ranking/offset information AND detail information in a single query.

Window functions are excellent for writing a single query that shows ranking information AND detail information in a single query.

To understand LAG and LEAD, we need to understand terms like “window frame” and “window order clause“. If you don’t know what those are, you should definitely check out my complete introduction to window functions:

SQL Server Window Functions: An introduction for beginners

2. What are the LAG and LEAD window functions?

LAG and LEAD will operate on each row in a partition, starting from the top and working it’s way down. They will return an element that is a specified offset from before or after the current row, respectively.

An element is simply a column value.

The LAG and LEAD functions takes three arguments:

1. The element you want to return
2. Offset value, which is just an integer. You can leave out this argument and it will default to 1.
3. A default value if there is no element at the specified offset. You can leave out this argument and it will default to NULL.

There are a few rules you need to remember about LAG and LEAD:

• They require the use of a window order clause
• A window frame clause is not supported.

If you’re not sure what a “window frame” or a “window order clause” are, it’s because you didn’t read the introduction to window functions!

3. Setting up some data

To understand how the LAG and LEAD functions works, we need to set up some data. If you have been following along with any of the other window function tutorials, you will already have this data set up in your environment.

Run these SELECT statements to see if you already have the data:

SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM Orders

The data should look like this:

If you don’t have the data, let’s go ahead and add it. Here’s the Customers table:

--Create Customers table
CREATE TABLE Customers
(
CustID INT PRIMARY KEY IDENTITY(50,5),
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

--Populate Customers it with data
INSERT INTO Customers(FirstName, LastName)
VALUES
('Joshua','Porter'),
('Andrew','Bluefield'),
('Jack','Donovan'),
('Cindy','Thatcher'),
('Gordon','Acres'),
('Gretchen','Hamilton')

The Products table:

--Create Products table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY IDENTITY(20,2),
ProductName VARCHAR(20),
Price DECIMAL(5,2)
)

--Populate Products table with data
INSERT INTO Products (ProductName, Price)
VALUES
('Large Bench',198.00),
('Small Bench',169.40),
('Coffee Table',220.00),
('Side Tables',265.20),
('Coat Rack',45.00)

Finally, the Orders table:

--Create Orders table
CREATE TABLE Orders
(
OrderID INT PRIMARY KEY IDENTITY(100,10),
CustID INT,
ProdID INT,
Qty TINYINT,
Orderdate DATETIME
)

--Populate Orders table with data
INSERT INTO Orders(CustID, ProdID, Qty, Orderdate)
VALUES
(55, 22, 1, '6/1/2021'),
(60, 28, 2, '6/6/2021'),
(75, 26, 1, '6/13/2021'),
(50, 20, 1, '7/1/2021'),
(55, 28, 1, '7/6/2021'),
(65, 24, 1, '7/14/2021'),
(55, 26, 1, '7/18/2021'),
(50, 26, 1, '7/24/2021'),
(70, 24, 1, '8/6/2021'),
(70, 26, 1, '8/6/2021'),
(70, 22, 3, '9/1/2021')

Notice the Orders table contains unofficial foreign key links to both the Customers and Products tables. I say they are “unofficial” because I didn’t actually create a foreign key constraint between the tables. It’s not necessary for this tutorial.

Let’s run a quick query with a couple of JOINS to see all the data laid out nicely for us:

SELECT C.CustID, C.FirstName, C.LastName, P.ProductName, P.Price as 'Product Price', 
O.Qty, O.OrderDate
FROM Orders as O
INNER JOIN Customers AS C
ON O.CustID = C.CustID
INNER JOIN Products as P
ON O.ProdID = P.ProductID

This query will give us the following result:

Nice. In this result set, we can see some really great details about each order. We can see the following details:

• Who made the order
• What they bought
• How much that product costs
• How many of that product they bought
• The date they made the order

Ok, now that we have some data set up, we can show you examples of using LAG and LEAD.

4. Example of using LAG

Let’s think of an example of some data we might want to see. What if we want to see a comparison between how much money a customer spent during a visit to our store versus how much money they spent during their previous visit to our store.

Ideally, we want to see that customers are spending more money every time they come to our store. We want to see that a customer spent more this time than they did last time.

LAG and LEAD are great for doing trend analysis like that.

Ok, let’s take a look at a good query to show us that information:

There are a few details to discuss about this query:

• The element in our LAG function is the Price column. This means the function will return the Price value from the offset row.
• I omitted the offset and default arguments in our LAG function call. They become 1 and NULL respectively. This means for every row in a partition, SQL will look for the Price value located 1 row behind the current row we’re on. If there simply is no row 1 row behind the current, NULL will be returned.
• We’re partitioning by CustID. This means we’re seeing a breakdown for each individual customer.
• Remember, neither LAG nor LEAD support a window frame.

LAG and LEAD operate on one row at a time, from top to bottom, in each partition

Let’s look specifically at rows for our customer Joshua Porter:

Let’s walk down these rows, one at a time, like SQL will do. It starts at the first row:

So remember, our LAG function is looking for the Price value 1 row behind the current row. Notice there simply is no row behind the current, so that’s why NULL is put as the value in our “Cost of previous Product purchased” column.

Then SQL moves on to the next row:

For this row, we once again want to grab the Price value 1 row behind us. This time, there is a value there, and it’s $198.00. We plug that value into our “Cost of previous product purchased” column.

Then we’re done with this partition. SQL will move on to the next one!

For giggles, let’s talk about the first row in the next partition (which contains rows for Andrew Bluefield)

We understand why we see NULL in our LAG column, right?

This is the first row in this partition. There simply is no row behind this row in this partition. Therefore, NULL is used!

5. Example of using LEAD

Folks, LEAD is very similar to LAG, except of course the difference is LEAD will return an element that is a specified offset from after (a.k.a in front of) the current row.

Let’s look at an example. This time, I’ll specify all three arguments:

I still want an offset of just 1, but instead of NULL as the default value, I chose to use 0.00.

(IMPORTANT: You should know that the default value you specify needs to be the same data type as your element. In my case, the element is Price, which is a decimal data type).

Again folks, it’s same same idea, but now we’re looking at the next row in the partition.

Let’s go down the list of orders for our customer Gordon Acres, starting with his first row:

If we look 1 row ahead of the current row, the Price is $265.20. We put that value in our LEAD column, which is called “Cost of next Product purchased“.

Move on to the next row:

One row ahead of this row has a Price of $169.40. Plug that value into our column.

Finally, the last row:

In this partition, there simply is no row one row ahead of the row we’re on, so the default value of 0.00 get’s plugged into our column.

Get it? Good.

6. Tips, tricks, and Links

Here is a list of a few tips and tricks you should know when working with the LAG and LEAD window functions:

Tip # 1: You can leave out a default value if you want to

This will work fine:

I specified the element name and the offset value, but NOT the default value. That’s fine.

Understand you can’t specify just an element and a default value, however.

Tip # 2: Remember, the offset value and default value default to 1 and NULL respectively

The only argument you are required to pass to LAG and LEAD is the name of an element. The offset value and default value are optional. If left out, they default to 1 and NULL respectively.

Links:

Everything discussed in this tutorial can be found in the following FREE Ebook:

Download your FREE SQL Server Window Functions EBook!

This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This Ebook will definitely be a great resource for you to reference throughout your career. Get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

The LAG and LEAD functions are two of four offset window functions available to us in Microsoft SQL Server. You need to understand all the different offset functions if you want to be the world’s best database developer. Take a look at the full tutorial on all the offset functions here:

SQL Server Offset Window Functions: FIRST_VALUE, LAST_VALUE, LAG, LEAD

Also, don’t forget to check out the four ranking window functions. It’s definitely important to know them, too:

SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Also, if you missed the introduction to window functions, it discusses the different aggregate window functions you can use in SQL Server. That tutorial is a MUST-READ, because it discusses all the parts involved in a window function that you need to know in order to write a window function query correctly. Don’t skip that tutorial, you can find it here:

SQL Server Window Functions: An introduction for beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!