Championship winner LeetCode Plus member exclusive question [detailed analysis] MySQL

Hello, I'm Lao Wu. You can also call me classmate Wu. Younger friends can also call me senior brother Wu. Welcome to the world of data analysis with me and learn together!

Interested friends can pay attention to me Data analysis column , there are many high-quality articles to share with you.
In addition, you are also welcome to pay attention to my SQL question brushing column , there are high-quality SQL questions I share and detailed analysis.

This blog post is also my SQL problem sharing link. In order to record my problem-solving process and prevent follow-up members from being unable to view the problem again after expiration, I will select a high-quality SQL problem for everyone to publish on my blog every day, and add some of my own problem-solving skills or practical knowledge points, hoping to be helpful to you.

So today's topic is the 1194 of LeetCode - the winner of the championship.

The following is a detailed description of the problem.

Problem description

Players player table

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id Is the primary key of this table.
Each row of this table represents each player's group.

Matches table

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id Is the primary key of this table.
Each line is a record of a game, first_player and second_player Indicates the player of the game ID. 
first_score and second_score Respectively represent first_player and second_player Score.
You can assume that in every game, the players belong to the same group.

The winner of each group is the player with the highest cumulative score in the group. If it's a draw, player_ The player with the smallest ID wins.

Write an SQL query to find the winners in each group.

The query result format is as follows:

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

Matches surface:
+------------+--------------+---------------+-------------+--------------+
| 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            |
+------------+--------------+---------------+-------------+--------------+

Result surface:
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+

Problem solving ideas

Connect the players and matches tables according to the same ID (first_player = player_id or second_player = player_id);
Then press group_id,player_id grouping.
Calculate the total score of each player in the group and sort according to this;
Finally, take out the first in each group.

code implementation

select 
    t.group_id,
    t.player_id
from (
    select
        p.group_id,
        p.player_id,
        row_number() over(
            partition by p.group_id 
            order by sum(if(p.player_id=m.first_player,m.first_score,m.second_score)) desc,p.player_id
        ) as rn
    from matches m,players p 
    where m.first_player = p.player_id or m.second_player = p.player_id
    group by p.group_id,p.player_id
) as t 
where t.rn = 1