Ranking athletes by medals earned
In chapter 1, you used ROW_NUMBER to rank athletes by awarded medals. However, ROW_NUMBER assigns different numbers to athletes with the same count of awarded medals, so it's not a useful ranking function; if two athletes earned the same number of medals, they should have the same rank.
This exercise is part of the course
PostgreSQL Summary Stats and Window Functions
Exercise instructions
- Rank each athlete by the number of medals they've earned -- the higher the count, the higher the rank -- with identical numbers in case of identical values.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH Athlete_Medals AS (
SELECT
Athlete,
COUNT(*) AS Medals
FROM Summer_Medals
GROUP BY Athlete)
SELECT
Athlete,
Medals,
-- Rank athletes by the medals they've won
___ OVER (ORDER BY ___ DESC) AS Rank_N
FROM Athlete_Medals
ORDER BY Medals DESC;