Most decorated athlete per region
Your goal for this exercise is to show the most decorated athlete per region. To set up this report, you need to leverage the ROW_NUMBER() window function, which numbers each row as an incremental integer, where the first row is 1, the second is 2, and so on.
Syntax for this window function is ROW_NUMBER() OVER (PARTITION BY field ORDER BY field). Notice how there is no argument within the initial function.
When set up correctly, a row_num = 1 represents the most decorated athlete within that region. Note that you cannot use a window calculation within a HAVING or WHERE statement, so you will need to use a subquery to filter.
Feel free to reference the E:R Diagram. We will use summer_games_clean to avoid null handling.
This exercise is part of the course
Reporting in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Query region, athlete_name, and total gold medals
____,
____ AS athlete_name,
____ AS total_golds,
-- Assign a regional rank to each athlete
____ AS row_num
FROM ____ AS s
JOIN ____ AS a
ON ____
JOIN ____ AS c
ON ____
GROUP BY ____;