# $1212. 查询球队积分
# https://leetcode-cn.com/problems/team-scores-in-football-tournament/

# SQL架构
Create table If Not Exists Teams (team_id int, team_name varchar(30));
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int);
Truncate table Teams;
insert into Teams (team_id, team_name) values ('10', 'Leetcode FC');
insert into Teams (team_id, team_name) values ('20', 'NewYork FC');
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC');
insert into Teams (team_id, team_name) values ('40', 'Chicago FC');
insert into Teams (team_id, team_name) values ('50', 'Toronto FC');
Truncate table Matches;
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0');
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0');

# Write your MySQL query statement below
select
    t.team_id,
    t.team_name,
    ifnull(score, 0) as num_points
from
    (
        select
            team_id,
            sum(score) as score
        from
            (
                select
                    host_team as team_id,
                    sum(
                        case
                            when host_goals > guest_goals then 3
                            when host_goals < guest_goals then 0
                            else 1
                        end
                    ) as score
                from
                    Matches
                group by
                    host_team
                union
                all
                select
                    guest_team as team_id,
                    sum(
                        case
                            when host_goals > guest_goals then 0
                            when host_goals < guest_goals then 3
                            else 1
                        end
                    ) as score
                from
                    Matches
                group by
                    guest_team
            ) tmp1
        group by
            team_id
    ) tmp2
    right join Teams t on t.team_id = tmp2.team_id
order by
    num_points desc,
    t.team_id;

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