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
Understanding the Visualization
1
Join Tables
Connect Players and Matches tables
2
Aggregate
Sum scores for each player
3
Rank
Find winner per group
Key Takeaway
🎯 Key Insight: Use window functions with proper ordering to handle both scoring and tie-breaking logic in a single query
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code