Group Stage Points Champions

Hard

A competitive gaming platform stores round-robin stage data in two tables. Table players: player_id: unique identifier of a player group_id: group to which the player belongs Table matches: match_id: unique identifier of a match first_player: one participant second_player: the other participant first_score: points awarded to first_player in this match second_score: points awarded to second_player in this match Business rules: Every match is played between two players from the same group. A player's season total is the sum of all points they receive across every match. Some players might appear in no matches; their total is then 0. Task: Return the champion for each group. Champion = player with the highest total points inside that group. Tie-break rule = choose the smallest player_id among tied players. Output requirements: Return exactly two columns: group_id, player_id Return exactly one row per group Output may be in any order (sorted output is acceptable) Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

players:
| player_id | group_id |
|-----------|----------|
| 15        | 1        |
| 25        | 1        |
| 30        | 1        |
| 45        | 1        |
| 10        | 2        |
| 35        | 2        |
| 50        | 2        |
| 20        | 3        |
| 40        | 3        |

matches:
| 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":1,"player_id":15},
  {"group_id":2,"player_id":35},
  {"group_id":3,"player_id":40}
]

Explanation

Group 1 totals are 15->3, 25->2, 30->3, 45->0, so tie at 3 is broken by smaller player_id (15). Group 2 winner is 35 with 1 point (tie with 50 broken by id). Group 3 winner is 40 with 5 points.

Example 2

Input

players:
| player_id | group_id |
|-----------|----------|
| 101       | 7        |
| 109       | 7        |
| 130       | 7        |

matches:
| match_id | first_player | second_player | first_score | second_score |
|----------|--------------|---------------|-------------|--------------|
| 1        | 101          | 130           | 4           | 0            |
| 2        | 109          | 130           | 4           | 0            |

Output

[
  {"group_id":7,"player_id":101}
]

Explanation

Players 101 and 109 both finish with 4 points, so the lower player_id (101) is selected.

Example 3

Input

players:
| player_id | group_id |
|-----------|----------|
| 1         | 10       |
| 3         | 10       |
| 2         | 11       |
| 4         | 11       |

matches:
| match_id | first_player | second_player | first_score | second_score |
|----------|--------------|---------------|-------------|--------------|
| 1        | 2            | 4             | 1           | 1            |

Output

[
  {"group_id":10,"player_id":1},
  {"group_id":11,"player_id":2}
]

Explanation

Group 10 has no matches, so all players are at 0 and the smallest id (1) wins. Group 11 players tie at 1 point, so smallest id (2) wins.

Constraints

1 <= number of rows in players <= 200,000
0 <= number of rows in matches <= 400,000
1 <= player_id, group_id, match_id <= 1,000,000,000
0 <= first_score, second_score <= 1,000
players.player_id is unique
matches.match_id is unique
first_player and second_player both exist in players
Each match is between players in the same group
Output must contain exactly one row per distinct group_id
If multiple players share max points within a group, return the smallest player_id
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas, return a DataFrame with columns [group_id, player_id]

players: | player_id | group_id | |-----------|----------| | 15 | 1 | | 25 | 1 | | 30 | 1 | | 45 | 1 | | 10 | 2 | | 35 | 2 | | 50 | 2 | | 20 | 3 | | 40 | 3 | matches: | 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 |

players: | player_id | group_id | |-----------|----------| | 101 | 7 | | 109 | 7 | | 130 | 7 | matches: | match_id | first_player | second_player | first_score | second_score | |----------|--------------|---------------|-------------|--------------| | 1 | 101 | 130 | 4 | 0 | | 2 | 109 | 130 | 4 | 0 |

players: | player_id | group_id | |-----------|----------| | 1 | 10 | | 3 | 10 | | 2 | 11 | | 4 | 11 | matches: | match_id | first_player | second_player | first_score | second_score | |----------|--------------|---------------|-------------|--------------| | 1 | 2 | 4 | 1 | 1 |