Tournament Winners - Problem

You are given two tables: Players and Matches.

The Players table contains information about each player and their group assignment. The Matches table records the results of matches between players, including their scores.

Key Requirements:

  • Players in each match belong to the same group
  • Find the winner in each group based on total points scored
  • In case of a tie, the player with the lowest player_id wins
  • Return the winner for each group

Table Schema

Players
Column Name Type Description
player_id PK int Unique identifier for each player
group_id int Group assignment for the player
Primary Key: player_id
Matches
Column Name Type Description
match_id PK int Unique identifier for each match
first_player int Player ID of the first player
second_player int Player ID of the second player
first_score int Points scored by the first player
second_score int Points scored by the second player
Primary Key: match_id

Input & Output

Example 1 — Basic Tournament
Input Tables:
Players
player_id group_id
1 1
2 1
3 2
Matches
match_id first_player second_player first_score second_score
1 1 2 10 20
Output:
group_id player_id
1 2
2 3
💡 Note:

In group 1, player 1 scored 10 points and player 2 scored 20 points, so player 2 wins. Player 3 is the only player in group 2, so they win by default with 0 points.
Example 2 — Tie Breaker
Input Tables:
Players
player_id group_id
1 1
2 1
Matches
match_id first_player second_player first_score second_score
1 1 2 15 15
Output:
group_id player_id
1 1
💡 Note:

Both players scored 15 points, creating a tie. The tie is broken by selecting the player with the lower player_id, which is player 1.
Example 3 — Multiple Matches
Input Tables:
Players
player_id group_id
1 1
2 1
3 1
Matches
match_id first_player second_player first_score second_score
1 1 2 10 5
2 2 3 8 12
3 1 3 7 3
Output:
group_id player_id
1 1
💡 Note:

Player 1 total: 10 + 7 = 17 points, Player 2 total: 5 + 8 = 13 points, Player 3 total: 12 + 3 = 15 points. Player 1 wins with the highest total score.

Constraints

  • 1 ≤ player_id ≤ 10000
  • 1 ≤ group_id ≤ 1000
  • 0 ≤ first_score, second_score ≤ 1000
  • Players in each match belong to the same group

Visualization

Tap to expand
Tournament Winners Find winner in each group by total points (tie: lowest player_id wins) INPUT Players Table player_id group_id 15 1 25 1 30 1 40 2 50 2 Matches Table p1 p2 p1_pts p2_pts 15 25 3 1 30 15 2 2 30 25 1 2 40 50 3 3 40 50 2 5 ALGORITHM STEPS 1 UNPIVOT Matches Combine p1/p2 into single player_id with their points 2 SUM Points per Player GROUP BY player_id SUM(points) as total 3 JOIN with Players Get group_id for each player from Players table 4 RANK within Groups ORDER BY total DESC, player_id ASC (for ties) Points Calculation (Group 1) Player 15: 3+2 = 5 pts Player 25: 1+2 = 3 pts Player 30: 2+1 = 3 pts Winner: 15 (5 pts) Group 2: 40(5) vs 50(8) FINAL RESULT Winners per Group Group 1 Player 15 5 total points Group 2 Player 50 8 total points Output Table group_id player_id 1 15 2 50 OK - Solution Complete Key Insight: Use UNION ALL to unpivot matches (combine first_player and second_player into rows), then SUM points per player. Apply RANK() OVER (PARTITION BY group_id ORDER BY total_pts DESC, player_id ASC) to find winner per group. The tie-breaker (lowest player_id) is handled by the secondary sort order. TutorialsPoint - Tournament Winners | Optimal Solution

Related Problems

Asked in
Amazon 15 Microsoft 12 Google 8
23.4K Views
Medium Frequency
~18 min Avg. Time
890 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