Evaluation Order and Common Problems
Window functions and GROUP BY
14. Ranking with Aggregate Functions
Summary and Review

Instruction

Thanks to how window functions behave with GROUP BY, it's fairly simple to create quite advanced statistic.

Let's take a look at other use cases.

For instance, we may create 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;

This query groups auctions by country, counts the number of auctions from each country and then creates a ranking based on the number of auctions.

Exercise

Group the auctions based on category. Show the CategoryId, the sum of final prices for all auctions in this category, and a ranking based on that sum. Have the highest sum come first. Name the last two columns SumFinalPrice and Ranking.

Stuck? Here's a hint!

Use

RANK() OVER(ORDER BY SUM(FinalPrice) DESC)

as your window function.