Sort the Olympic Table

Problem

Table: Olympic

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country       | varchar |
| gold_medals   | int     |
| silver_medals | int     |
| bronze_medals | int     |
+---------------+---------+
In SQL, country is the primary key for this table.
Each row in this table shows a country name and the number of gold, silver, and bronze medals it won in the Olympic games.

The Olympic table is sorted according to the following rules:

The country with more gold medals comes first.
If there is a tie in the gold medals, the country with more silver medals comes first.
If there is a tie in the silver medals, the country with more bronze medals comes first.
If there is a tie in the bronze medals, the countries with the tie are sorted in ascending order lexicographically.

Write a solution to sort the Olympic table.

The result format is shown in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


Input: 
Olympic table:
+-------------+-------------+---------------+---------------+
| country     | gold_medals | silver_medals | bronze_medals |
+-------------+-------------+---------------+---------------+
| China       | 10          | 10            | 20            |
| South Sudan | 0           | 0             | 1             |
| USA         | 10          | 10            | 20            |
| Israel      | 2           | 2             | 3             |
| Egypt       | 2           | 2             | 2             |
+-------------+-------------+---------------+---------------+
Output: 
+-------------+-------------+---------------+---------------+
| country     | gold_medals | silver_medals | bronze_medals |
+-------------+-------------+---------------+---------------+
| China       | 10          | 10            | 20            |
| USA         | 10          | 10            | 20            |
| Israel      | 2           | 2             | 3             |
| Egypt       | 2           | 2             | 2             |
| South Sudan | 0           | 0             | 1             |
+-------------+-------------+---------------+---------------+
Explanation: 
The tie between China and USA is broken by their lexicographical names. Since "China" is lexicographically smaller than "USA", it comes first.
Israel comes before Egypt because it has more bronze medals.

Solution

Method 1 - Multi-level ORDER BY

Intuition: We need to implement the Olympic ranking system where countries are ranked by gold medals first, then silver, then bronze, and finally alphabetically by name for ties.

Approach:

Use ORDER BY with multiple columns to implement the ranking hierarchy
Sort by gold_medals DESC (more gold medals first)
Then by silver_medals DESC (more silver medals first for ties in gold)
Then by bronze_medals DESC (more bronze medals first for ties in silver)
Finally by country ASC (alphabetical order for complete ties)

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


SELECT 
    country,
    gold_medals,
    silver_medals,
    bronze_medals
FROM Olympic
ORDER BY 
    gold_medals DESC,
    silver_medals DESC,
    bronze_medals DESC,
    country ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


import java.sql.*;
import java.util.*;

public class Solution {
    public List<Map<String, Object>> sortOlympicTable(Connection connection) throws SQLException {
        String sql = """
            SELECT country, gold_medals, silver_medals, bronze_medals
            FROM Olympic
            ORDER BY gold_medals DESC, silver_medals DESC, bronze_medals DESC, country ASC
            """;
        
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (PreparedStatement stmt = connection.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("country", rs.getString("country"));
                row.put("gold_medals", rs.getInt("gold_medals"));
                row.put("silver_medals", rs.getInt("silver_medals"));
                row.put("bronze_medals", rs.getInt("bronze_medals"));
                result.add(row);
            }
        }
        
        return result;
    }
}

1
2
3
4
5
6
7
8


import pandas as pd

def sortOlympicTable(olympic: pd.DataFrame) -> pd.DataFrame:
    # Sort by multiple columns with specified order
    return olympic.sort_values(
        by=['gold_medals', 'silver_medals', 'bronze_medals', 'country'],
        ascending=[False, False, False, True]  # DESC, DESC, DESC, ASC
    ).reset_index(drop=True)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


def sortOlympicTable(olympic_data):
    """
    olympic_data: List of dictionaries with keys: country, gold_medals, silver_medals, bronze_medals
    """
    # Custom sorting key function
    def sort_key(row):
        return (
            -row['gold_medals'],    # Negative for descending order
            -row['silver_medals'],  # Negative for descending order
            -row['bronze_medals'],  # Negative for descending order
            row['country']          # Positive for ascending order
        )
    
    return sorted(olympic_data, key=sort_key)

Complexity

⏰ Time complexity: O(n log n) where n is the number of countries (sorting operation)
🧺 Space complexity: O(1) for SQL query, O(n) for result storage in programming languages

Problem#

Examples#

Solution#

Method 1 - Multi-level ORDER BY#

Code#

Complexity#