Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Introduction
LEAD() and LAG()
11. The LAG(x, y) function
FIRST_VALUE(), LAST_VALUE()
Summary

Instruction

Nice! And, of course, there's also an corresponding version of LAG(x, y) with two arguments:

SELECT
  Name,
  OpenDate,
  LAG(OpenDate,2) OVER(ORDER BY OpenDate ASC) AS Lag
FROM Website;

Now, instead of the previous row, we show the value "2 rows before".

Some people have difficulties remembering the difference between LEAD(...) and LAG(...). You can think about it this way: imagine that your ordered rows are people hiking in the mountains. The people (rows) that are further ahead are currently LEADing, so you get them with LEAD(). The people (rows) behind you are currently LAGging (coming behind), so you call them with LAG().

Exercise

Show the following statistics for the website with Id = 3: Day, Revenue, and the Revenue 3 days before. Name the last column Lag.

Stuck? Here's a hint!

Use LAG(Revenue,3) and the right ORDER BY clause.