Evaluation Order and Common Problems
Window functions and GROUP BY
17. Grouped Rows, Window Functions, and PARTITION BY
Summary and Review

Instruction

Now you can use window functions and PARTITION BY on grouped rows. One thing you need to remember is that the window function will only see grouped rows, not the original rows. Take a look:

SELECT
  Country,
  EndDate,
  SUM(Views) AS ViewsOnDay,
  SUM(SUM(Views)) OVER(PARTITION BY Country) AS ViewsCountry
FROM Auction
GROUP BY Country,
  EndDate
ORDER BY Country ASC,
  EndDate ASC;

The query might require a bit of explanation. First of all, we grouped all rows by the Country and EndDate. Then, we showed the country name and the auction end date. Look what happens in the next two columns. First, we simply sum the views in accordance with our GROUP BY clause, i.e. we get the sum of views for all auctions in one country on that particular day. But look what happens next. We use a window function to add all the daily sums for each country. As a result, we get the total number of views for a particular country on all days.

Exercise

Group all auctions by category and end date and show the following columns:

  • CategoryId
  • EndDate
  • the average daily final price as DailyAvgFinalPrice for that category on that day
  • the maximal daily average (as DailyMaxAvg) in that category for any day

Stuck? Here's a hint!

Use

MAX(AVG(FinalPrice))
in the window function.