Tournament winners per group

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

A sports league stores players in tournament_players(player_id, group_id) and results in tournament_matches(match_id, first_player, second_player, first_score, second_score). Find the winner in each group. The winner is the player with the highest total score in that group. In the event of a tie, the player with the lowest player_id wins. Return the result table with group_id and player_id, ordered by group_id.

Requirements

• Output columns: group_id, player_id.
• Sort output by group_id ascending.

Constraints

• Scores are non-negative integers.
• Ties must be broken by lowest player_id.

Examples

Input: tournament_players player_id,group_id 15,1 25,1 30,1 45,1 10,2 35,2 50,2 tournament_matches match_id,first_player,second_player,first_score,second_score 1,15,45,3,0 2,30,25,1,2 3,45,15,1,1 4,10,35,5,3 5,35,50,2,0 6,20,40,2,2 ... Output: group_id | player_id 1 | 15 2 | 10 3 | 20 Why this passes: In group 1 player 15 scores 3 + 1 = 4 in total, the highest of the group, so 15 wins; if two players tie on total score the lower player_id would win.

Input: tournament_players player_id | group_id 10 | 1 20 | 1 tournament_matches match_id | first_player | second_player | first_score | second_score 1 | 10 | 20 | 5 | 1 Output: group_id | player_id 1 | 10 Why this passes: Player 10 scored 5 points versus Player 20 with 1 point, winning group 1.

Topics: lakebench, sql, unpivot, tie-break.

More SQL interview questions · All interview problems · Learn data engineering