Summer Deals - hours only!Up to 80% off on all courses and bundles.-Close
Window functions and GROUP BY
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.