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

Window functions and GROUP BY
Summary and Review

Instruction

Great! We can also use LEAD and LAG with grouped rows. The query below creates day-to-day deltas. Take a look:

SELECT
  EndDate,
  SUM(FinalPrice) AS SumPrice,
  LAG(SUM(FinalPrice)) OVER(ORDER BY EndDate ASC)
FROM Auction
GROUP BY EndDate
ORDER BY EndDate ASC;

This shows each end date with

  1. the total price of all items sold on that day and
  2. the total price of all items sold on the previous day.

Exercise

For each end date, show the following:

  • the EndDate
  • the sum of views from auctions that ended on that day (name this column SumViews)
  • the sum of views from the previous day (name this column PreviousDay)
  • the difference between the sum of views on that day and on the previous day (name this column Delta)

Stuck? Here's a hint!

You will have to use:

LAG(SUM(Views))

in the window function.