Grand Slam Titles solution and explanation
3 more videos on this problem
LeetCode Medium 1783 Interview SQL Question with Detailed Explanation | Practice SQLEveryday Data Science · 14:19 · 7K views
From Rookie to Rockstar: Mastering Advanced SQL Interview Questions! - Data Science | Leetcode 1783Everyday Data Science · 6:31 · 3K views
LeetCode 1783: Grand Slam Titles [SQL]Frederik Müller · 9:17 · 2K views
LeetCode 1783. Grand Slam TitlesYuki Kitayama · 9:21 · 17 views
The 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.
Example 1:
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.
Approach overview
Problem Overview: The database stores tennis tournament winners for four Grand Slam events (Wimbledon, French Open, US Open, and Australian Open) in separate columns for each year. The task is to compute how many Grand Slam titles each player has won and return their player_id, player_name, and total titles.
Approach 1: Union All + Equi-Join + Group By (O(n) time, O(n) space)
The championships table stores winners across four different columns, which means the first step is converting those columns into a single column of player IDs. Use UNION ALL to vertically combine the four tournament columns into one result set containing all winners. This creates a normalized stream of champion records where each row represents one title. After that, perform an equi-join with the Players table to map each player_id to the corresponding player_name. Finally, apply GROUP BY player_id and count the number of appearances to compute the total Grand Slam titles. The query scans the championship records once and aggregates efficiently, giving overall O(n) time where n is the number of rows in the championships table.
This approach works well because SQL aggregation is designed for exactly this kind of counting task. UNION ALL avoids unnecessary duplicate elimination overhead that comes with UNION. The combination of vertical flattening and aggregation is a common pattern in SQL interview questions involving denormalized schemas.
Approach 2: Derived Table Aggregation (O(n) time, O(n) space)
Another way to structure the same logic is by building a derived table (subquery) that lists all winners first, then aggregating in an outer query. The inner query performs four SELECT statements on each tournament column and combines them with UNION ALL. This derived table effectively represents every title ever awarded as individual rows. The outer query then joins that result with the Players table and uses GROUP BY to compute the count per player.
The benefit of this structure is clarity. Separating the normalization step from the aggregation step makes the query easier to reason about during interviews or debugging. Databases optimize derived tables efficiently, so the complexity remains O(n) time and O(n) intermediate space.
Both approaches rely heavily on database fundamentals such as joins and aggregations. Understanding how to reshape data with UNION ALL and summarize results using GROUP BY is the key insight.
Recommended for interviews: The Union All + Join + Group By approach is the expected solution. Interviewers want to see that you recognize the schema is denormalized and know how to transform multiple columns into rows before aggregating. Writing the union clearly and grouping correctly demonstrates solid SQL fundamentals.
Approach 1: Union All + Equi-Join + Group By
We can use UNION ALL to merge all player IDs who won Grand Slam titles into a table T, then use an equi-join JOIN to join T table with Players table on player_id, and finally use GROUP BY and COUNT to count the number of Grand Slam titles won by each player.
# Write your MySQL query statement below
WITH
T AS (
SELECT Wimbledon AS player_id
FROM Championships
UNION ALL
SELECT Fr_open AS player_id
FROM Championships
UNION ALL
SELECT US_open AS player_id
FROM Championships
UNION ALL
SELECT Au_open AS player_id
FROM Championships
)
SELECT player_id, player_name, COUNT(1) AS grand_slams_count
FROM
T
JOIN Players USING (player_id)
GROUP BY 1;Approach 2: Default Approach
# Write your MySQL query statement below
SELECT
player_id,
player_name,
SUM(
(
CASE
WHEN Wimbledon = player_id THEN 1
ELSE 0
END
) + (
CASE
WHEN Fr_open = player_id THEN 1
ELSE 0
END
) + (
CASE
WHEN US_open = player_id THEN 1
ELSE 0
END
) + (
CASE
WHEN Au_open = player_id THEN 1
ELSE 0
END
)
) AS grand_slams_count
FROM
Championships
CROSS JOIN Players
GROUP BY player_id
HAVING grand_slams_count > 0;Complexity comparison
| Approach | Complexity |
|---|---|
| Union All + Equi-Join + Group By | – |
| Default Approach | – |
Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Union All + Equi-Join + Group By | O(n) | O(n) | Best general solution when multiple columns represent similar entities and must be normalized before aggregation |
| Derived Table Aggregation | O(n) | O(n) | Useful when you want clearer query structure by separating normalization and aggregation steps |
Frequently asked questions
Is Grand Slam Titles easy or hard?
Grand Slam Titles is rated Medium because it requires recognizing that multiple tournament columns must be normalized before aggregation. Once you identify the UNION ALL pattern, the rest of the solution becomes a straightforward JOIN and GROUP BY query.
Grand Slam Titles Python/Java solution
This problem is a SQL database question rather than a typical algorithm implemented in Python or Java. The solution is written as a MySQL query using UNION ALL, JOIN, and GROUP BY to compute the number of Grand Slam titles per player.
How to solve Grand Slam Titles in O(n)?
Flatten the four tournament columns (Wimbledon, French Open, US Open, Australian Open) using UNION ALL so each title appears as a separate row. Join that result with the Players table to get player names, then apply GROUP BY player_id and COUNT(*) to compute total titles. The database performs a linear scan and aggregation, giving O(n) complexity.
What is the best approach for Grand Slam Titles?
The most effective approach uses UNION ALL to combine the four tournament winner columns into a single list of player IDs, then joins with the Players table and aggregates using GROUP BY. This converts a denormalized schema into a simple counting problem. The query runs in O(n) time where n is the number of championship rows.
Is Grand Slam Titles asked at Google/Amazon/Meta?
SQL aggregation and schema‑reshaping problems like Grand Slam Titles commonly appear in database interview rounds at companies such as Amazon, Meta, and other data-focused roles. The question tests joins, UNION ALL usage, and GROUP BY aggregation rather than complex algorithms.
What data structure is used in Grand Slam Titles?
The solution relies on relational database operations rather than traditional data structures. Key SQL constructs include UNION ALL to reshape columns into rows, JOIN to map player IDs to names, and GROUP BY with COUNT for aggregation.
What is the time complexity of Grand Slam Titles?
The SQL query runs in O(n) time because each row in the Championships table is scanned once for each UNION ALL branch and then aggregated. Since the number of tournaments per row is constant (four columns), the total complexity remains linear with respect to the number of years stored.