When window functions are evaluated
Window functions and GROUP BY
Summary

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_price in 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.

Console

Code editor

Result

TableConsole