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()
8. The LEAD(x, y) function
FIRST_VALUE(), LAST_VALUE()
Summary

Instruction

Fantastic! There is also another version of LEAD. It takes two arguments: LEAD(x, y). The x argument remains the same – it specifies the column to return. The y argument is an offset – it defines how many rows we will use (going forward from the current row). For instance:

SELECT
  Name,
  OpenDate,
  LEAD(OpenDate,2) OVER(ORDER BY OpenDate ASC) AS Lead
FROM Website;

This form of LEAD() won't show the webpage that has the opening date immediately after the current opening date. Instead, it will show the opening date two rows ahead – the first row will show the third date, etc. It's offsetting the result by two rows.

Exercise

There's a website with Id = 2. Find its statistics between May 1 and May 14, 2016. Show the Day, the number of users, and the number of users seven days later. Name the column Lead.

Notice that the last seven rows don't have a value in the last column – there are no rows "7 days from now" for them.

Stuck? Here's a hint!

Use LEAD(Users,7) and the right ORDER BY clause.