Skip to main content
Talentd

Grand Slam Titles - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon
Practice this problem

Problem Statement

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.

Code

MySQL

MySQL

Try this approach in the editor →

Approach 2: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Union All + Equi-Join + Group By—
Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Union All + Equi-Join + Group ByO(n)O(n)Best general solution when multiple columns represent similar entities and must be normalized before aggregation
Derived Table AggregationO(n)O(n)Useful when you want clearer query structure by separating normalization and aggregation steps

Video Solution

LeetCode Medium 1783 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 7,410 views views

Watch 3 more video solutions →

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.

Ready to solve this problem?

Practice Grand Slam Titles with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary