SQL LEAD Function

The SQL Server LEAD function is one of the Analytic functions that allows access to the data from a subsequent row without using any SELF JOIN or Subquery. The LEAD function is handy when comparing the current and next row values.

The SQL Server LEAD function has to be followed by the ORDER BY clause and an optional PARTITION BY clause. The ORDER BY clause sorts the table or partition in ascending or descending order based on the given column, and the LEAD function retrieves the next row within the partition following that particular order.

In real-time, you can use this SQL Server LEAD function to compare current sales with the next row of sales to identify the sales trend over time. This article explains the LEAD function syntax, parameters, and practical examples.

SQL Server LEAD function syntax

The basic syntax of the LEAD function to access the following row in a given column is as shown below:

SELECT LEAD([Scalar Expression], [Offset], [Default]) 
                  OVER (
                         PARTITION_BY_Clause 
                         ORDER_BY_Clause
                        )
FROM [Source]

Let’s see the LEAD Function syntax parameters and their significance in a query.

  • Scalar Expression: Please specify the Column Name, Subquery, or expression from which you want to retrieve the successive row(s) value. It returns a single value.
  • Default Value: It is an optional parameter; here, you must specify a default value. By default, if no subsequent row is available, the SQL LEAD function returns NULL. However, if you pass the default value, NULLs will be replaced with this value. If you omit default_value, this function writes NULL.
  • Offset Value: An optional parameter determines the number of rows you want to look forward to. For instance, an offset of 1 means the next row, and 2 means it selects the 2nd row (i.e., row after the next row). Again, it can be a Column, Subquery, or expression that returns a single value.
  • PARTITION BY Clause: It divides the table records chosen by the SELECT Statement into multiple groups or partitions based on the given column. If you define the PARTITION BY Clause, the SQL Server LEAD Function is bound to that group and selects the subsequent rows in each partition. The LEAD function will consider all the table records as a single partition if you don’t.
  • ORDER BY Clause: A mandatory parameter that helps to sort the partitioned (or complete table) data in ascending or descending order. Please refer to the Order By Clause page in SQL Server. The LEAD function returns the following row based on this order.

SQL Server LEAD Function Examples

This section covers multiple examples to understand the LEAD function better and how the PARTITION BY clause changes the complete result set. We also explain the importance of the Offset and default values in handling NULLs.

Remember, Microsoft introduced the IGNORE NULLS and RESPECT NULLS keywords in SQL Server 2022, and you can use them to handle NULL in the SQL Server LEAD function scalar expression.

  • RESPECT NULLS: It is the default one, and you can ignore this keyword. With or without this word returns the same result. For instance, LEAD(Sales) OVER(PARTITION BY Income) is the same as the LEAD(Sales) RESPECT NULLS OVER(PARTITION BY Income)
  • IGNORE NULLS: It will ignore any null values in the scalar expression and return the last non-null value. LEAD(Sales) IGNORE NULLS OVER(PARTITION BY Income).

For this SQL LEAD Function demonstration, we will use a simple table with 12 records derived from the AdventureWorksDW2022, having sales, orders, year, and quarter columns. Please use the query below to export the sales information from the Fact Internet Sales table to the SalesOrders table. The query that you must use is as shown below.

SELECT  YEAR(OrderDate) AS Year, DATEPART(q, OrderDate) AS Quarter, SUM(OrderQuantity) AS Orders,
SUM(SalesAmount) AS Sales
INTO Sample.dbo.SalesOrders
FROM [FactInternetSales]
WHERE YEAR(OrderDate) NOT IN(2010, 2014)
GROUP BY YEAR(OrderDate), DATEPART(q, OrderDate)
ORDER BY Year, Quarter

SQL LEAD Function without a Default Value

As we mentioned earlier in the LEAD function syntax, it is optional to use the Default value, Offset value, and PARTITION BY clause. This LEAD Function example explains what happens if we miss the PARTITION BY Clause and the Default value. In the series of examples, we use the AdventureWorksDW database.

First, the ORDER BY [Year] will sort the employees based on the Sales year and Quarter in ascending order. Next, the SQL Server LEAD function returns the subsequent row value (the next value from the row) as the output. If there are no rows to return, it returns NULL because we haven’t defined any default value.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales) OVER (ORDER BY Year, Quarter) AS Out1 
FROM [SalesOrders]
ORDER BY Year, Quarter

You can see from the image below that the last row in the Out1 column is NULL because there is no next row and a default value. If you want to use the emp table that we mentioned in the LAG article, you can try the code below.

SELECT [Name] ,[Education] ,[Occupation] ,[YearlyIncome],
      Year,[Sales]
      ,LEAD([Sales]) OVER (ORDER BY [Year]) AS Out1 
  FROM emp

NOTE: The LEAD function must require the ORDER BY clause; otherwise, it will throw an error.

SQL LEAD Function with a Default Value to handle NULLs

Let me modify the above LEAD Function example to add the Offset as 1, and the default value is 0. Although the default value of Offset is 1, you have to mention it before using the default value because it is the second argument. If you miss the Offset value, 0 becomes the offset, and no default value will replace NULLs.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales, 1, 0) OVER (ORDER BY Year, Quarter) AS Out2
FROM [SalesOrders]
ORDER BY Year, Quarter

Now you can see the SQL LEAD function replaces the NULL value of December with 0. It means the Default Value Parameter will handle the NULLs returned by the LEAD function. Please refer to the LAG Function and SELF JOIN articles.

All the above-mentioned examples use multiple columns in the ORDER BY clause, but you can also use a single column. For instance, the query below uses Quarter to sort the orders and returns the next sales record for each quarter.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales) OVER (ORDER BY Quarter) AS Out1 
FROM [SalesOrders]
ORDER BY Quarter

TIP: While passing the default value to the LEAD function, you must be careful with the data type. Both the column data type and the default value data type must match; otherwise, it throws an error.

Change SQL Server LEAD Function Offset Value

As we mentioned before, the default offset value is 1. What happens if we change the offset value in the LEAD Function from the default 1 to 2?

If you observe the screenshot below, the LEAD function selects the 2nd row (jumping one row) rather than the next subsequent row. If you change the value to 3, it selects the third row by dropping the first two rows, etc.

If you observe the Out3, the first row value is the 2011 Quarter 3 sales (not Quarter 2). Next, the last two quarters of the year 2013 are 0 because there are no next records after them.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales, 2, 0) OVER (ORDER BY Year, Quarter) AS Out3
FROM [SalesOrders]
ORDER BY Year, Quarter

SQL Server LEAD Function with Partition By Clause

This example explains the LEAD function for returning the following values from the partitioned records in a table. In all the above examples, we didn’t use the PARTITION BY clause, so this LEAD function considers the whole table as a single partition. When you use the PARTITION BY clause, it groups the table records based on the given column.

The following query will group (partition) the SalesOrders table data by Year. Next, the ORDER BY clause will sort each partition based on its Year and Quarter in ascending order. Finally, the SQL LEAD function retrieves the subsequent Sales values in each year partition.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales, 1, 0) OVER (PARTITION BY Year ORDER BY Year, Quarter) AS Out1 
FROM [SalesOrders]
ORDER BY Year, Quarter

As you can see, for the years 2011, 2012, and 2013 fourth quarter rows, the LEAD function return 0 because all those records are the last records of their partition. Therefore, there are no subsequent records to select within that partition.

If you miss providing the Offset and default values, those 0s will be replaced by NULLs. For this, replace LEAD([Sales], 1, 0) with LEAD([Sales]) in the above code.

Compare Two Rows and Calculate the Difference

In real-time, you can use the SQL LEAD function to compare the current sales against the next year, quarter, month, week, or day sales. In such a case, you can use the LEAD function and perform some mathematical calculations.

The query below uses the LEAD function to find the next quarter’s sales in each year. Next, subtract that amount from the current quarter’s sales to see the growth. And finally, calculate the growth percentage.

SELECT [Year]
      ,[Quarter]
      ,[Orders]
      ,[Sales]
      ,LEAD(Sales, 1, 0) OVER (PARTITION BY Year ORDER BY Year, Quarter) AS NextQ
      ,LEAD(Sales, 1, 0) OVER (PARTITION BY Year ORDER BY Year, Quarter) - Sales AS Growth
      ,CONCAT(ROUND((LEAD(Sales) OVER 
      (PARTITION BY Year ORDER BY Year, Quarter) - Sales)/ 
        NULLIF(Sales, 0) * 100,0),'%') AS Grow_Percent
FROM [SalesOrders]
ORDER BY Year, Quarter
SQL LEAD Function Example

The Role of PARTITION BY in LEAD

The above query explains the SQL Server LEAD Function with the PARTITION BY clause. It selects the subsequent rows within each Year group. However, if we change the PARTITION BY column, it returns an entirely new Sales value. Let me change the partition column to Education to understand it better.

The query below returns the Sales of subsequent rows for each Education group instead of the Occupation partition. The reference table for the example below is taken from the LAG article.

SELECT [Name] ,[Occupation] ,[Education]
      ,[YearlyIncome], Year,[Sales]
      ,LEAD([Sales], 1, 0) OVER (
				  PARTITION BY [Education]
				  ORDER BY [Year]
				) AS CompareSale  
FROM [emp]
Categories SQL