When window functions are evaluated
Window functions and GROUP BY
Summary

Instruction

Great. As you can see, it's fairly simple to create quite advanced statistics very easily thanks to how window functions behave with GROUP BY. Let's take a look at other use cases.

For instance, we may make a ranking based on an aggregate function. Take a look:

SELECT
  country,
  COUNT(id),
  RANK() OVER(ORDER BY COUNT(id) DESC)
FROM auction
GROUP BY country;

We grouped auctions with respect to the country, counted the number of auctions from each country... and then we created a ranking based on that count of auctions.

Exercise

Now, group the auctions based on the category. Show category_id, the sum of final prices for auctions from this category and a ranking based on that sum, with the highest sum coming first.

Stuck? Here's a hint!

Use

RANK() OVER(ORDER BY SUM(final_price) DESC)
as your window function.

Console

Code editor

Result

TableConsole