SQL Interview Questions – Grand Slam Titles

Problem Description –

Write an SQL query 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 query result format is in the following example.

Problem Link – Grand slam titles

Difficulty Level – Medium

Solution –

with result as (
SELECT
    player_id,
    COUNT(*) as grand_slams_count
FROM (
SELECT wimbledon as player_id
FROM Championships
UNION ALL 
SELECT Fr_open 
FROM Championships
UNION ALL 
SELECT US_open 
FROM Championships
UNION ALL 
SELECT Au_open 
FROM Championships
) x
GROUP BY 1
)

SELECT
    result.player_id,
    players.player_name,
    result.grand_slams_count
FROM players JOIN result
ON players.player_id = result.player_id