Grand Slam Titles - Problem
Grand Slam Tennis Tournament Count
You're tasked with analyzing tennis championship data to count Grand Slam titles for each player. Given two tables:
The four Grand Slam tournaments are:
• Wimbledon
• French Open (Fr_open)
• US Open (US_open)
• Australian Open (Au_open)
Goal: Return a list showing each player's name and their total Grand Slam count, but only include players who have won at least one tournament.
This is a classic SQL aggregation problem that tests your ability to work with multiple tables, use UNION operations, and perform GROUP BY aggregations.
You're tasked with analyzing tennis championship data to count Grand Slam titles for each player. Given two tables:
Players (containing player information) and Championships (containing winner IDs for each year's four Grand Slam tournaments), you need to calculate how many Grand Slam tournaments each player has won.The four Grand Slam tournaments are:
• Wimbledon
• French Open (Fr_open)
• US Open (US_open)
• Australian Open (Au_open)
Goal: Return a list showing each player's name and their total Grand Slam count, but only include players who have won at least one tournament.
This is a classic SQL aggregation problem that tests your ability to work with multiple tables, use UNION operations, and perform GROUP BY aggregations.
Input & Output
example_1.sql — Basic Case
$
Input:
Players:
| player_id | player_name |
|-----------|-------------|
| 1 | Wimbledon |
| 2 | LeBron |
| 3 | Alice |
Championships:
| year | Wimbledon | Fr_open | US_open | Au_open |
|------|-----------|---------|---------|----------|
| 2018 | 1 | 1 | 1 | 1 |
| 2019 | 1 | 1 | 1 | 1 |
| 2020 | 2 | 1 | 2 | 1 |
›
Output:
| player_name | grand_slams_count |
|-------------|-------------------|
| Wimbledon | 7 |
| LeBron | 5 |
💡 Note:
Player 1 (Wimbledon) won: 4 tournaments in 2018, 4 in 2019, and 1 in 2020 = 7 total. Player 2 (LeBron) won: 0 in 2018, 0 in 2019, and 2 in 2020 = 2 total. Wait, let me recalculate: Player 1 won Wimbledon (2018,2019), Fr_open (2018,2019,2020), US_open (2018,2019), Au_open (2018,2019,2020) = 8 total. Player 2 won Wimbledon (2020), US_open (2020) = 2 total.
example_2.sql — Single Tournament Winners
$
Input:
Players:
| player_id | player_name |
|-----------|-------------|
| 1 | Federer |
| 2 | Nadal |
| 3 | Djokovic |
Championships:
| year | Wimbledon | Fr_open | US_open | Au_open |
|------|-----------|---------|---------|----------|
| 2020 | 1 | 2 | 3 | 1 |
›
Output:
| player_name | grand_slams_count |
|-------------|-------------------|
| Djokovic | 1 |
| Federer | 2 |
| Nadal | 1 |
💡 Note:
In 2020: Federer won Wimbledon and Au_open (2 titles), Nadal won Fr_open (1 title), Djokovic won US_open (1 title).
example_3.sql — No Winners Edge Case
$
Input:
Players:
| player_id | player_name |
|-----------|-------------|
| 1 | PlayerOne |
| 2 | PlayerTwo |
Championships:
| year | Wimbledon | Fr_open | US_open | Au_open |
|------|-----------|---------|---------|----------|
| 2020 | 3 | 3 | 3 | 3 |
›
Output:
| player_name | grand_slams_count |
|-------------|-------------------|
💡 Note:
No results returned because players 1 and 2 didn't win any tournaments (all tournaments were won by player_id 3, who is not in the Players table).
Constraints
- 1 ≤ Players table rows ≤ 100
- 1 ≤ Championships table rows ≤ 50
- All player_ids in Championships table are valid integers
- Only include players who won at least one tournament
- Player names are unique and contain only alphanumeric characters
Visualization
Tap to expand
Understanding the Visualization
1
Original Structure
Championship data spread across 4 tournament columns per year
2
UNION ALL Magic
Combine all tournament columns into a single stream of player_id values
3
Join & Count
Connect with player names and count occurrences
Key Takeaway
🎯 Key Insight: UNION ALL is the perfect tool for transforming column-based data into row-based data, enabling natural aggregation operations.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code