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.
Group all auctions by the category and end date and show the following columns:
category_id,ended,daily_avg_final_price in that category on that day,daily_max_avg.Use
MAX(AVG(final_price))in the window function.
