Grand Slam Titles - Practice Coding Problems

Grand Slam Titles - Problem

Database Medium

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	3
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

Understanding the Visualization

Input Tables

Players and Championships with tournament columns

UNION ALL

Combine tournament columns into single column

GROUP BY

Count wins per player and join with names

Key Takeaway

🎯 Key Insight: Use UNION ALL to transform columns into rows when you need to count across multiple columns

Asked in

a Amazon 12 M Microsoft 8 G Google 6

Use UNION ALL to combine all four tournament columns into rows, then GROUP BY player to count total wins. JOIN with Players table to get player names.

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

Common Approaches

Approach	Time	Space	Notes
✓ UNION ALL with GROUP BY	O(n * m)	O(n * m)	Combine all tournament columns into rows, then count wins per player

UNION ALL with GROUP BY — Algorithm Steps

Step 1: Use UNION ALL to combine Wimbledon, Fr_open, US_open, Au_open columns
Step 2: GROUP BY player_id and COUNT the total wins
Step 3: JOIN with Players table to get player names

Visualization

Tap to expand

Step-by-Step Walkthrough

Original Data

Championships with separate columns

UNION ALL

Combine all tournament columns

GROUP BY

Count wins per player

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n * m)

n years × m tournaments, plus grouping

✓ Linear Growth

Space Complexity

O(n * m)

Temporary table from UNION operations

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

UNION ALL with GROUP BY — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler