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:
CategoryIdEndDate- the average daily final price as
DailyAvgFinalPricefor 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.



