# $1194. 锦标赛优胜者
# https://leetcode-cn.com/problems/tournament-winners/

# SQL架构
Create table If Not Exists Players (player_id int, group_id int);
Create table If Not Exists Matches (match_id int, first_player int, second_player int, first_score int, second_score int);
Truncate table Players;
insert into Players (player_id, group_id) values ('10', '2');
insert into Players (player_id, group_id) values ('15', '1');
insert into Players (player_id, group_id) values ('20', '3');
insert into Players (player_id, group_id) values ('25', '1');
insert into Players (player_id, group_id) values ('30', '1');
insert into Players (player_id, group_id) values ('35', '2');
insert into Players (player_id, group_id) values ('40', '3');
insert into Players (player_id, group_id) values ('45', '1');
insert into Players (player_id, group_id) values ('50', '2');
Truncate table Matches;
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('1', '15', '45', '3', '0');
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('2', '30', '25', '1', '2');
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('3', '30', '15', '2', '0');
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('4', '40', '20', '5', '2');
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('5', '35', '50', '1', '1');

# Write your MySQL query statement below
select
    tmp2.group_id,
    any_value(tmp2.player_id) as player_id
from
    (
        select
            tmp1.group_id,
            tmp1.player_id,
            sum(tmp1.score) as score
        from
            (
                select
                    p.group_id,
                    p.player_id,
                    sum(m.first_score) as score
                from
                    Players p
                    join Matches m on p.player_id = m.first_player
                group by
                    p.group_id,
                    p.player_id
                union
                all
                select
                    p.group_id,
                    p.player_id,
                    sum(m.second_score) as score
                from
                    Players p
                    join Matches m on p.player_id = m.second_player
                group by
                    p.group_id,
                    p.player_id
            ) tmp1
        group by
            tmp1.group_id,
            tmp1.player_id
        order by
            # 每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小 的选手获胜。
            score desc,
            tmp1.player_id
    ) tmp2
group by
    tmp2.group_id;

# clean-up
drop table Players;
drop table Matches;