Evaluation Order and Common Problems
Window functions and GROUP BY
16. Day-to-Day Deltas with 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.