SQL Tutorial - Window Functions - Lag and Lead

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.

In this video I talk about using Lag and Lead in Windows Functions.

T-SQL Querying
https://www.amazon.com/gp/product/0735685045/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735685045&linkCode=as2&tag=beardeddev05-20&linkId=e93b0f7483b1fa06aca5a3aec4f7764b

T-SQL Fundamentals
https://www.amazon.com/gp/product/150930200X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=150930200X&linkCode=as2&tag=beardeddev05-20&linkId=70aa2ffa3e84cab66cb964ca1e9cee62

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/0735658366/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735658366&linkCode=as2&tag=beardeddev05-20&linkId=b88746aea666c9f98da147e70b4491ca

This video covers the requirements to pass the exam:
If you would like to follow along with the tutorial please run the SQL below:

IF OBJECT_ID(N'dbo.Sales', N'U') IS NOT NULL
DROP TABLE dbo.Sales;

GO

CREATE TABLE dbo.Sales
(
Sales_Id INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_Sales_Sales_Id PRIMARY KEY
, Sales_Customer_Id INT NOT NULL
, Sales_Date DATETIME2 NOT NULL
, Sales_Amount DECIMAL (16, 2) NOT NULL
)

INSERT INTO dbo.Sales (Sales_Customer_Id, Sales_Date, Sales_Amount)
VALUES
(1, '20180102', 54.99)
, (1, '20180103', 72.99)
, (1, '20180104', 34.99)
, (1, '20180115', 29.99)
, (1, '20180121', 67.00)

Lag and Lead are useful for performing trend analysis, in the example I show how we can display a customer spending trend.

Lag will show the previous value.
Lead will show the next value.

Lag and Lead accept multiple parameters as demonstrated in the video:

LAG([Column], [Offset], [Value if NULL])

The example of LAG and LEAD in the video can be shown by executing the below SQL query:

SELECT
Sales_Customer_Id
, Sales_Date
, LAG(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS PrevValue
, Sales_Amount
, LEAD(Sales_Amount, 2, 0) OVER(PARTITION BY Sales_Customer_Id ORDER BY Sales_Date) AS NextValue
FROM dbo.Sales

Please feel free to post comments.

Видео SQL Tutorial - Window Functions - Lag and Lead канала BeardedDev