# $1841. 联赛信息统计
# https://leetcode.cn/problems/league-statistics/

# SQL架构
Create table If Not Exists Teams (team_id int, team_name varchar(20));
Create table If Not Exists Matches (home_team_id int, away_team_id int, home_team_goals int, away_team_goals int);
Truncate table Teams;
insert into Teams (team_id, team_name) values ('1', 'Ajax');
insert into Teams (team_id, team_name) values ('4', 'Dortmund');
insert into Teams (team_id, team_name) values ('6', 'Arsenal');
Truncate table Matches;
insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('1', '4', '0', '1');
insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('1', '6', '3', '3');
insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('4', '1', '5', '2');
insert into Matches (home_team_id, away_team_id, home_team_goals, away_team_goals) values ('6', '1', '0', '0');

# Write your MySQL query statement below
select
    distinct team_name,
    count(*) as matches_played,
    sum(
        case
            when (
                team_id = home_team_id
                and home_team_goals > away_team_goals
            )
            or (
                team_id = away_team_id
                and home_team_goals < away_team_goals
            ) then 3
            when (
                team_id = home_team_id
                and home_team_goals < away_team_goals
            )
            or (
                team_id = away_team_id
                and home_team_goals > away_team_goals
            ) then 0
            else 1
        end
    ) as points,
    sum(
        if(
            team_id = home_team_id,
            home_team_goals,
            away_team_goals
        )
    ) as goal_for,
    sum(
        if(
            team_id = home_team_id,
            away_team_goals,
            home_team_goals
        )
    ) as goal_against,
    sum(
        if(
            team_id = home_team_id,
            home_team_goals,
            away_team_goals
        )
    ) - sum(
        if(
            team_id = home_team_id,
            away_team_goals,
            home_team_goals
        )
    ) as goal_diff
from
    Teams
    join Matches on team_id = home_team_id
    or team_id = away_team_id
group by
    team_name,
    team_id
order by
    points desc,
    goal_diff desc,
    team_name;

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