Grand Slam Titles - Problem

You are given information about tennis players and their Grand Slam tournament wins. The Players table contains player information, and the Championships table contains the winners of each Grand Slam tournament by year.

Table: Players

  • player_id (int): Primary key, unique identifier for each player
  • player_name (varchar): Name of the tennis player

Table: Championships

  • year (int): Primary key, tournament year
  • Wimbledon (int): Player ID who won Wimbledon
  • Fr_open (int): Player ID who won French Open
  • US_open (int): Player ID who won US Open
  • Au_open (int): Player ID who won Australian Open

Write a SQL query to report the number of Grand Slam tournaments won by each player. Do not include players who did not win any tournament. Return the result in any order.

Table Schema

Players
Column Name Type Description
player_id PK int Primary key, unique identifier for each player
player_name varchar Name of the tennis player
Primary Key: player_id
Championships
Column Name Type Description
year PK int Primary key, tournament year
Wimbledon int Player ID who won Wimbledon that year
Fr_open int Player ID who won French Open that year
US_open int Player ID who won US Open that year
Au_open int Player ID who won Australian Open that year
Primary Key: year

Input & Output

Example 1 — Multiple Tournament Winners
Input Tables:
Players
player_id player_name
1 Novak Djokovic
2 Rafael Nadal
3 Roger Federer
Championships
year Wimbledon Fr_open US_open Au_open
2018 1 2 1 3
2019 1 2 2 1
Output:
player_name grand_slams_count
Novak Djokovic 4
Rafael Nadal 3
Roger Federer 1
💡 Note:

Novak Djokovic (ID=1) won Wimbledon 2018, US Open 2018, and Australian Open 2019 = 3 titles. Rafael Nadal (ID=2) won French Open 2018, French Open 2019, and US Open 2019 = 3 titles. Roger Federer (ID=3) won Australian Open 2018 = 1 title.
Example 2 — Single Tournament Winner
Input Tables:
Players
player_id player_name
1 Serena Williams
2 Simona Halep
Championships
year Wimbledon Fr_open US_open Au_open
2020 2 2 2 2
Output:
player_name grand_slams_count
Simona Halep 4
💡 Note:

Simona Halep (ID=2) won all four Grand Slam tournaments in 2020, giving her 4 titles. Serena Williams (ID=1) did not win any tournaments, so she is excluded from the result.

Constraints

  • 1 ≤ player_id ≤ 1000
  • 1980 ≤ year ≤ 2030
  • player_name consists of English letters and spaces only
  • Each tournament winner player_id exists in the Players table

Visualization

Tap to expand
Grand Slam Titles - SQL Solution INPUT TABLES Players Table player_id player_name 1 Novak Djokovic 2 Rafael Nadal 3 Roger Federer Championships Table year Wimb French US Aus 2020 1 2 1 1 2021 1 1 2 1 2022 1 2 3 2 ALGORITHM STEPS 1 UNPIVOT Columns Transform 4 tournament cols into rows using UNION ALL 2 JOIN Players Connect player_id with player names 3 GROUP BY Player Aggregate wins per player using COUNT(*) 4 Filter Results Include only players with at least 1 win (HAVING) SELECT p.player_name, COUNT (*) AS grand_slams FROM unpivoted u JOIN Players p... GROUP BY player_name FINAL RESULT Grand Slam Counts player_name titles Novak Djokovic 7 Rafael Nadal 4 Roger Federer 1 OK - Query Complete 3 players with titles 1st Key Insight: The Championships table stores each tournament winner as a separate column. To count total wins per player, we must UNPIVOT the 4 tournament columns into rows using UNION ALL, then JOIN with Players and GROUP BY to aggregate. This transforms a wide table into a tall format suitable for counting. TutorialsPoint - Grand Slam Titles | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen