1. Learn
    2. /
  2. Courses
    3. /
  3. Reporting in SQL

Exercise

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.

Instructions 1/2

undefined XP
    1
    2
  • Build a query that pulls region, athlete_name, and total_golds by joining summer_games_clean, athletes, and countries.
  • Add a field called row_num that uses ROW_NUMBER() to assign a regional rank to each athlete based on total golds won.