Grand Slam Titles

Problem

Table: Players

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| player_id      | int     |
| player_name    | varchar |
+----------------+---------+
player_id is the primary key (column with unique values) for this table.
Each row in this table contains the name and the ID of a tennis player.

Table: Championships

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| year          | int     |
| Wimbledon     | int     |
| Fr_open       | int     |
| US_open       | int     |
| Au_open       | int     |
+---------------+---------+
year is the primary key (column with unique values) for this table.
Each row of this table contains the IDs of the players who won one each tennis tournament of the grand slam.

Write a solution to report the number of grand slam tournaments won by each player. Do not include the players who did not win any tournament.

Return the result table in any order.

The result format is 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
25
26
27
28


Input: 
Players table:
+-----------+-------------+
| player_id | player_name |
+-----------+-------------+
| 1         | Nadal       |
| 2         | Federer     |
| 3         | Novak       |
+-----------+-------------+
Championships table:
+------+-----------+---------+---------+---------+
| year | Wimbledon | Fr_open | US_open | Au_open |
+------+-----------+---------+---------+---------+
| 2018 | 1         | 1       | 1       | 1       |
| 2019 | 1         | 1       | 2       | 2       |
| 2020 | 2         | 1       | 2       | 2       |
+------+-----------+---------+---------+---------+
Output: 
+-----------+-------------+-------------------+
| player_id | player_name | grand_slams_count |
+-----------+-------------+-------------------+
| 2         | Federer     | 5                 |
| 1         | Nadal       | 7                 |
+-----------+-------------+-------------------+
Explanation: 
Player 1 (Nadal) won 7 titles: Wimbledon (2018, 2019), Fr_open (2018, 2019, 2020), US_open (2018), and Au_open (2018).
Player 2 (Federer) won 5 titles: Wimbledon (2020), US_open (2019, 2020), and Au_open (2019, 2020).
Player 3 (Novak) did not win anything, we did not include them in the result table.

Solution

Method 1 – Union All and Group By

Intuition

Each row in the Championships table has the winner’s player_id for each tournament. We can unpivot the table to get all winners in a single column, then count the number of times each player_id appears. Join with the Players table to get player names.

Approach

Use UNION ALL to unpivot the Championships table, collecting all player_ids from all tournaments into one column.
Group by player_id and count the number of wins as grand_slams_count.
Join the result with the Players table to get player_name.
Exclude players with zero wins.

Code

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


SELECT c.player_id, p.player_name, COUNT(*) AS grand_slams_count
FROM (
    SELECT Wimbledon AS player_id FROM Championships
    UNION ALL
    SELECT Fr_open FROM Championships
    UNION ALL
    SELECT US_open FROM Championships
    UNION ALL
    SELECT Au_open FROM Championships
) c
JOIN Players p ON c.player_id = p.player_id
GROUP BY c.player_id, p.player_name;

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


SELECT c.player_id, p.player_name, COUNT(*) AS grand_slams_count
FROM (
    SELECT "Wimbledon" AS player_id FROM "Championships"
    UNION ALL
    SELECT "Fr_open" FROM "Championships"
    UNION ALL
    SELECT "US_open" FROM "Championships"
    UNION ALL
    SELECT "Au_open" FROM "Championships"
) c
JOIN "Players" p ON c.player_id = p.player_id
GROUP BY c.player_id, p.player_name;

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


class Solution:
    def grand_slam_titles(self, players, championships):
        # players: DataFrame with columns ['player_id', 'player_name']
        # championships: DataFrame with columns ['year', 'Wimbledon', 'Fr_open', 'US_open', 'Au_open']
        import pandas as pd
        ids = pd.concat([
            championships['Wimbledon'],
            championships['Fr_open'],
            championships['US_open'],
            championships['Au_open']
        ], ignore_index=True)
        cnt = ids.value_counts().reset_index()
        cnt.columns = ['player_id', 'grand_slams_count']
        res = pd.merge(cnt, players, on='player_id')
        return res[['player_id', 'player_name', 'grand_slams_count']]

Complexity

⏰ Time complexity: O(n) — Each tournament result is processed once.
🧺 Space complexity: O(n) — For the unpivoted/intermediate table.

Problem#

Examples#

Solution#

Method 1 – Union All and Group By#

Intuition#

Approach#

Code#

Complexity#