The SQL LEAD is one of the Analytic Function. This function allows you to access the data from a subsequent row without using any SELF JOIN. The basic syntax of the LEAD in SQL Server is as shown below:
SELECT LEAD([Scalar Expression], [Offset], [Default]) OVER ( PARTITION_BY_Clause ORDER_BY_Clause ) FROM [Source]
- Scalar Expression: Column, Subquery, or expression that returns a single value.
- Default: Specify a default value. If you omit it, it writes NULL
- Offset: Number of rows you want to forward. For instance, If it is 2, then it selects the 2nd row as a result. Again, it can be a Column, Subquery, or expression which returns a single value.
- Order_By_Clause: Sort the Partitioned data in a particular order. Please refer to Order By Clause in SQL Server.
- Partition_By_Clause: Divide the records chosen by the SELECT Statement into partitions.
- If you defined the Partition_By_Clause, the LEAD Function starts selecting the subsequent rows in each partition.
- If you haven’t, it will consider all the records as a single partition.
We are going to use the below-shown data for this SQL LEAD demonstration

SQL LEAD without Partition By Clause
What will happen if we miss the Partition By Clause in the Sql Server LEAD Function?
The order by statement will sort the Employee table based on their Yearly Income data in an Ascending order.
Next, the SQL lead function returns the subsequent value of the row (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 [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,LEAD([Sales]) OVER (ORDER BY [YearlyIncome]) AS CompareSale FROM [Employee]

Let me add the Offset as 1, and the default value is 0
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,LEAD([Sales], 1, 0) OVER ( ORDER BY [YearlyIncome] ) AS CompareSale FROM [Employee]

Now you can see that the lead function is replacing NULL value with 0. Please refer to LAG and SELF JOIN articles.
SQL LEAD with Partition By Example
How to write the subsequent values from the partitioned records present in a table?
The following query will group the data by Occupation using their yearly Income, and then subsequent Sales values in each partition.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,LEAD([Sales], 1, 0) OVER ( PARTITION BY [Occupation] ORDER BY [YearlyIncome] ) AS CompareSale FROM [Employee]
As you can see, for the fourth, 8th, 12th, 14th row, SQL Server Lead function returns 0 because there are no subsequent records to select within that partition. All those records are last records of their partition.

In the above query, we used SQL LEAD Function with Partition by clause. It selects the subsequent rows within the individual partition.
Let me change the partition column to education to understand it better.
SELECT [FirstName] ,[LastName] ,[Occupation] ,[Education] ,[YearlyIncome] ,[Sales] ,LEAD([Sales], 1, 0) OVER ( PARTITION BY [Education] ORDER BY [YearlyIncome] ) AS CompareSale FROM [Employee]

Hope you understand :)
Lead Offset Value
What happens if we change the offset value in the SQL LEAD Function offset value from default 1 to 2.
SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,LEAD([Sales], 2, 0) OVER ( PARTITION BY [Occupation] ORDER BY [YearlyIncome] ) AS CompareSale FROM [Employee]
If you observe the below screenshot, the SQL server lead function is selecting the 2nd row (jumping one row) rather than selecting the next subsequent row. If you change the value to 3, it selects the third row by dropping the first two rows, etc.
