Grand Slam Titles

Grand Slam Titles

What is this problem about?

The Grand Slam Titles coding problem is a SQL task. You are given a Players table (containing player IDs and names) and a Championships table. The Championships table has columns for the year and the four major tennis tournaments (Wimbledon, Fr_open, US_open, Au_open). Each tournament column contains the player_id of the winner for that year. You need to write a query to report the player name and the total number of Grand Slam titles they have won. Do not include players who haven't won any titles.

Why is this asked in interviews?

Amazon uses this Database interview pattern to test your ability to "unpivot" data or count occurrences across multiple columns simultaneously. It evaluates your knowledge of SUM with CASE statements or your ability to use UNION ALL to transform wide data (multiple tournament columns) into a long format for easier aggregation.

Algorithmic pattern used

There are two main SQL patterns to solve this:

1. Multiple Joins / Conditional Sums: Join the Players table with the Championships table. Use a sum of case statements or simple equality checks across the four columns: SUM((Wimbledon = player_id) + (Fr_open = player_id) + ...)
2. UNION ALL (Unpivoting): Create a subquery that unifies all winners from all tournaments into a single column. SELECT Wimbledon AS id FROM Championships UNION ALL SELECT Fr_open... Then JOIN this subquery with the Players table, group by player, and count.

Example explanation

Championships:

Year	Wimbledon	Fr_open	US_open	Au_open
2018	1	1	1	1
2019	1	1	2	2

Using the Conditional Sum approach for Player 1:

• 2018: 1 (W) + 1 (Fr) + 1 (US) + 1 (Au) = 4 titles.
• 2019: 1 (W) + 1 (Fr) + 0 (US) + 0 (Au) = 2 titles. Total for Player 1 = 6 titles.

Common mistakes candidates make

• Using COUNT instead of SUM: If you join the tables and write COUNT(Wimbledon = player_id), it will count the row regardless of whether the condition is true or false. You must use SUM(CASE WHEN ... THEN 1 ELSE 0 END).
• Using UNION instead of UNION ALL: If you use the unpivot method, UNION will remove duplicate wins (e.g., if a player won Wimbledon twice, it would only count as 1). UNION ALL preserves all wins.

Interview preparation tip

The UNION ALL approach is highly recommended for "unpivoting" columns. It’s conceptually cleaner and less prone to logic errors than writing massive conditional sums across many columns.