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
Quick Refresher
PARTITION BY ORDER BY with Ranking
PARTITION BY ORDER BY with Window Frames
Using PARTITION BY ORDER BY with Analytical Functions
16. LEAD() with PARTITION BY ORDER BY
Summary and Review

Instruction

Now, let's talk about the use of analytical functions with PARTITION BY ORDER BY. Take a look at the following example:

SELECT
  Country,
  City,
  OpeningDate,
  LEAD(City,1,'NaN') OVER(PARTITION BY Country ORDER BY OpeningDate ASC) AS FirstOpenCity
FROM Store;

In the above example, we show the Country, City, and OpeningDate of each store, but we also show the city (in the same country) where the next store was opened.

Exercise

For each store, show its sales for the period of August 5-10, 2016. Show the StoreId, Day, number of transactions, number of transactions on the previous day (PreviousTransactions) and the Difference between these two values.

Stuck? Here's a hint!

Use LAG(Transactions).