Instruction
Perfect. Finally, you can use window functions with 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,
ended,
SUM(views) AS views_on_day,
SUM(SUM(views)) OVER(PARTITION BY country)
AS views_country
FROM auction
GROUP BY country, ended
ORDER BY country, ended
The query might require a bit of explanation. First of all, we grouped all rows by the country and ended. Then, we showed the country name and date when the auctions ended. 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 in all auctions from the particular country on the particular day. But look what happens next. We use a window function to sum all daily sums for a particular country. As a result, we get the sum of views for a particular country on all days.
Exercise
Group all auctions by the category and end date and show the following columns:
category_id,ended,- the average daily final price as
daily_avg_final_pricein that category on that day, - the maximal daily average in that category from any day as
daily_max_avg.
Stuck? Here's a hint!
Use
MAX(AVG(final_price))in the window function.
