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.
Latihan ini adalah bagian dari kursus
PostgreSQL Summary Stats and Window Functions
Petunjuk latihan
- 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.
Latihan interaktif praktis
Cobalah latihan ini dengan menyelesaikan kode contoh berikut.
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;