Description
Table: Players
| Column Name | Type |
|---|---|
| player_id | int |
| group_id | int |
player_idis 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_idis the primary key (column with unique values) of this table.- Each row is a record of a match,
first_playerandsecond_playercontain theplayer_idof each match. first_scoreandsecond_scorecontain the number of points of thefirst_playerandsecond_playerrespectively.- You may assume that, in each match, players belong to the same group.
Problem Statement
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:
Playerstable:
| player_id | group_id |
|---|---|
| 15 | 1 |
| 25 | 1 |
| 30 | 1 |
| 45 | 1 |
| 10 | 2 |
| 35 | 2 |
| 50 | 2 |
| 20 | 3 |
| 40 | 3 |
Matchestable:
| 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 |
Solution
The problem looks difficult but it’s not so much. Essentially, we need score per player. This is union of first_player score and second_player score.
1WITH unioned_scores AS (
2 SELECT first_player AS player_id, first_score AS score
3 FROM Matches
4 UNION ALL
5 SELECT second_player AS player_id, second_score AS score
6 FROM Matches
7) SELECT * FROM unioned_scores;
| player_id | score |
|---|---|
| 15 | 3 |
| 30 | 1 |
| 30 | 2 |
| 40 | 5 |
| 35 | 1 |
| 45 | 0 |
| 25 | 2 |
| 15 | 0 |
| 20 | 2 |
| 50 | 1 |
Once you have the unioned_score, you can calculate score per player using GROUP BY clause.
1SELECT player_id, SUM(score) AS total_score
2FROM unioned_scores
3GROUP BY player_id
| player_id | total_score |
|---|---|
| 15 | 3 |
| 30 | 3 |
| 40 | 5 |
| 35 | 1 |
| 45 | 0 |
| 25 | 2 |
| 20 | 2 |
| 50 | 1 |
Next, you need to rank players per group. This can be done using RANK(), DENSE_RANK() or ROW_NUMBER() functions. For this the window clause should be PARTITION BY group_id ORDER BY total_score DESC, player_id ASC.
1WITH unioned_scores AS (
2 SELECT first_player AS player_id, first_score AS score
3 FROM Matches
4 UNION ALL
5 SELECT second_player AS player_id, second_score AS score
6 FROM Matches
7), scores_per_player AS (
8 SELECT player_id, SUM(score) AS total_score
9 FROM unioned_scores
10 GROUP BY player_id
11), ranked_players AS (
12 SELECT
13 group_id, Players.player_id,
14 DENSE_RANK() OVER (PARTITION BY group_id ORDER BY total_score DESC, player_id ASC) AS rnk
15 FROM scores_per_player
16 JOIN Players ON scores_per_player.player_id = Players.player_id
17) SELECT * FROM ranked_players order by rnk
| group_id | player_id | rnk |
|---|---|---|
| 1 | 15 | 1 |
| 2 | 35 | 1 |
| 3 | 40 | 1 |
| 1 | 30 | 2 |
| 2 | 50 | 2 |
| 3 | 20 | 2 |
| 1 | 25 | 3 |
| 1 | 45 | 4 |
The last thing is to filter only those rows with rnk = 1. This can be done using WHERE clause. This is what brings you the final result.
1WITH unioned_scores AS (
2 SELECT first_player AS player_id, first_score AS score
3 FROM Matches
4 UNION ALL
5 SELECT second_player AS player_id, second_score AS score
6 FROM Matches
7), scores_per_player AS (
8 SELECT player_id, SUM(score) AS total_score
9 FROM unioned_scores
10 GROUP BY player_id
11), ranked_players AS (
12 SELECT
13 group_id, Players.player_id,
14 DENSE_RANK() OVER (PARTITION BY group_id ORDER BY total_score DESC, player_id ASC) AS rnk
15 FROM scores_per_player
16 JOIN Players ON scores_per_player.player_id = Players.player_id
17) SELECT group_id, player_id FROM ranked_players
18 WHERE rnk = 1;
Comments