LeetCode: Team Scores in Football Tournament Posted on August 5, 2019July 26, 2020 by braindenny Team Scores in Football Tournament Similar Problems: CheatSheet: SQL & MySql CheatSheet: Leetcode For Code Interview CheatSheet: Common Code Problems & Follow-ups Tag: #sql 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 Post navigation LeetCode: Queries Quality and PercentageLeetCode: Valid Palindrome III Leave a Reply Cancel replyYour email address will not be published.Comment Name Email Website