Table: Players
+-------------+-------+ | Column Name | Type | +-------------+-------+ | player_id | int | | group_id | int | +-------------+-------+ player_id is the primary key (column with unique values) of this table. Each row of this table indicates the group of each player.
Table: Matches
+---------------+---------+ | Column Name | Type | +---------------+---------+ | match_id | int | | first_player | int | | second_player | int | | first_score | int | | second_score | int | +---------------+---------+ match_id is the primary key (column with unique values) of this table. Each row is a record of a match, first_player and second_player contain the player_id of each match. first_score and second_score contain the number of points of the first_player and second_player respectively. You may assume that, in each match, players belong to the same group.
The winner in each group is the player who scored the maximum total points within the group. In the case of a tie, the lowest player_id wins.
Write a solution to find the winner in each group.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Players table: +-----------+------------+ | player_id | group_id | +-----------+------------+ | 15 | 1 | | 25 | 1 | | 30 | 1 | | 45 | 1 | | 10 | 2 | | 35 | 2 | | 50 | 2 | | 20 | 3 | | 40 | 3 | +-----------+------------+ Matches table: +------------+--------------+---------------+-------------+--------------+ | match_id | first_player | second_player | first_score | second_score | +------------+--------------+---------------+-------------+--------------+ | 1 | 15 | 45 | 3 | 0 | | 2 | 30 | 25 | 1 | 2 | | 3 | 30 | 15 | 2 | 0 | | 4 | 40 | 20 | 5 | 2 | | 5 | 35 | 50 | 1 | 1 | +------------+--------------+---------------+-------------+--------------+ Output: +-----------+------------+ | group_id | player_id | +-----------+------------+ | 1 | 15 | | 2 | 35 | | 3 | 40 | +-----------+------------+
Problem Overview: Each player belongs to a tournament group and plays matches that produce scores. The task is to compute the total score for every player and return the winner of each group. If multiple players tie with the same score, the player with the smallest player_id wins the group.
Approach 1: Aggregate Scores + Join With Group Maximum (O(n log n) time, O(n) space)
Start by computing the total score per player. Each match contributes points to two players, so you normalize the data using UNION ALL to produce a single stream of (player_id, score). Then use GROUP BY player_id to sum scores. After computing totals, join this result with the Players table to associate each player with a group_id. For each group, compute the maximum total score and filter players that match it. Finally, resolve ties using MIN(player_id). This approach relies on standard SQL aggregation and joins, making it portable across most SQL engines.
Approach 2: Aggregation + Window Ranking (O(n log n) time, O(n) space)
A cleaner solution uses window functions. First compute each player's total score using the same normalized match table and GROUP BY. Join the aggregated scores with the Players table to attach group information. Then apply RANK() or ROW_NUMBER() over PARTITION BY group_id ORDER BY total_score DESC, player_id ASC. This ranking guarantees the highest score appears first while automatically resolving ties by the smallest player ID. Selecting rows where rank equals 1 directly returns the winner of each group. This pattern is common in analytical SQL problems and keeps the query compact and easy to maintain.
Both approaches depend heavily on GROUP BY aggregation to compute total scores. The window function variant is typically preferred because it avoids nested joins and expresses the ranking logic directly in SQL.
Recommended for interviews: The aggregation + window ranking approach. Interviewers expect you to normalize match results, compute player totals, and then rank players inside each group. Demonstrating the aggregation logic shows understanding of relational data, while using window functions shows strong SQL proficiency.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Aggregate Scores + Join With Group Maximum | O(n log n) | O(n) | Works in SQL systems without window functions or when sticking to basic aggregation and joins |
| Aggregation + Window Ranking | O(n log n) | O(n) | Best for modern SQL engines like MySQL/PostgreSQL where window functions simplify ranking logic |
Leetcode HARD 1194 - Tournament Winners MULTI-COLUMN JOIN TRICK - Explained by Everyday Data Science • Everyday Data Science • 1,147 views views
Practice Tournament Winners with our built-in code editor and test cases.
Practice on FleetCode