Using the LEAD Function in SQL Server

Just as the LAG function can look at a previous row in a data set, the LEAD function can look ahead. I went through an example of the LAG function so now it’s time to take a look at LEAD.

Cleveland Rocks

For our LEAD function example, we’ll work with a couple Cleveland Browns player contracts for recent NFL seasons.

We’ll create a table to hold our contract info:

CREATE TABLE BrownsContracts
(
	Id INT IDENTITY(1,1) NOT NULL,
	Player VARCHAR(100) NOT NULL,
	ContractYear SMALLINT NOT NULL,
	YearlyCash INT NOT NULL
);
GO

And insert a couple players along with their contracts for 2018-2022:

INSERT INTO BrownsContracts
VALUES ('Baker Mayfield',2018,22329440),
	('Myles Garrett',2018,1847375),
	('Baker Mayfield',2019,24326411),
	('Myles Garrett',2019,2105605),
	('Baker Mayfield',2020,27941905),
	('Myles Garrett',2020,21914442),
	('Baker Mayfield',2021,33048675),
	('Myles Garrett',2021,21684000),
	('Baker Mayfield',2022,51906675),
	('Myles Garrett',2022,19000000);

Leading the League

To view a contract for each season and also show the contract for the next (LEAD) season, we can run this query:

SELECT Player
	,ContractYear
	,YearlyCash
	,LEAD(YearlyCash) OVER (
		PARTITION BY Player ORDER BY ContractYear
		) AS LeadCash
FROM BrownsContracts;
GO

We’ve partitioned by Player, ordered by ContractYear, and LeadCash will be YearlyCash for the following row of a player.

Show Me the Money

Those contracts are pretty large and one long integer isn’t the easiest way to read. Let’s try to format our results in dollar amounts to make them more readable:

SELECT Player
	,ContractYear
	,FORMAT(YearlyCash, 'C') AS 'YearlyCash'
	,FORMAT(LEAD(YearlyCash) OVER (
			PARTITION BY Player ORDER BY ContractYear
			), 'C') AS LeadCash
FROM BrownsContracts;
GO

Since we’re not as concerned with change, we can use ‘C0’ instead of ‘C’ in our formatting. Something tells me pennies here are not much of a concern.

SELECT Player
	,ContractYear
	,FORMAT(YearlyCash, 'C0') AS 'YearlyCash'
	,FORMAT(LEAD(YearlyCash) OVER (
			PARTITION BY Player ORDER BY ContractYear
			), 'C0') AS LeadCash
FROM BrownsContracts;
GO

Don’t Forget LEAD and LAG!

LEAD and LAG can be just the solution needed for some tricky situations. I was introducing these to someone awhile back and their face lit up realizing the ways these could solve some issues.

Thanks for reading!