LeetCode: Team Scores in Football Tournament

Posted on August 5, 2019July 26, 2020 by braindenny

Team Scores in Football Tournament

Similar Problems:

Given an integer array of size n, find all elements that appear more than n/3 times. The algorithm should run in linear time and in O(1) space.

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.

Solution: left join on two fields

## https://code.dennyzhang.com/team-scores-in-football-tournament
select Teams.team_id, Teams.team_name, 
    sum(case when team_id=host_team and host_goals>guest_goals then 3 else 0 end) +
    sum(case when team_id=host_team and host_goals=guest_goals then 1 else 0 end) +
    sum(case when team_id=guest_team and host_goals<guest_goals then 3 else 0 end) +
    sum(case when team_id=guest_team and host_goals=guest_goals then 1 else 0 end) as num_points
from Teams left join Matches
on Teams.team_id = Matches.host_team or Teams.team_id = Matches.guest_team
group by Teams.team_id
order by num_points desc, Teams.team_id asc

Solution: sum + if

## https://code.dennyzhang.com/team-scores-in-football-tournament
select Teams.team_id, Teams.team_name, sum(if(isnull(num_points), 0, num_points)) as num_points
from Teams left join
    (
        select host_team as team_id,
            sum(case when host_goals>guest_goals then 3 
                     when host_goals=guest_goals then 1
                     else 0 end) as num_points
        from Matches
        group by host_team
        union all
        select guest_team as team_id,
            sum(case when host_goals<guest_goals then 3 
                     when host_goals=guest_goals then 1
                     else 0 end) as num_points
        from Matches
        group by guest_team
    ) as tt
on Teams.team_id = tt.team_id
group by Teams.team_id
order by num_points desc, Teams.team_id asc

Solution: if + sum

## https://code.dennyzhang.com/team-scores-in-football-tournament
select Teams.team_id, Teams.team_name, ifnull(sum(num_points), 0) as num_points
from Teams left join
    (
        select host_team as team_id,
            sum(case when host_goals>guest_goals then 3 
                     when host_goals=guest_goals then 1
                     else 0 end) as num_points
        from Matches
        group by host_team
        union all
        select guest_team as team_id,
            sum(case when host_goals<guest_goals then 3 
                     when host_goals=guest_goals then 1
                     else 0 end) as num_points
        from Matches
        group by guest_team
    ) as tt
on Teams.team_id = tt.team_id
group by Teams.team_id
order by num_points desc, Teams.team_id asc

Post Views: 0