Skip to content

Prepare For Coder Interview – Denny

  • Basic
  • Medium
  • Hard
  • Architect
  • Life

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
linkedin
github
slack

Post Views: 0
Posted in MediumTagged sql

Post navigation

LeetCode: Queries Quality and Percentage
LeetCode: Valid Palindrome III

Leave a Reply Cancel reply

Your email address will not be published.

Tags

#array #backtracking #bfs #binarytree #bitmanipulation #blog #classic #codetemplate #combination #dfs #dynamicprogramming #game #graph #greedy #heap #inspiring #interval #linkedlist #manydetails #math #palindrome #recursive #slidingwindow #stack #string #subarray #trie #twopointer #twosum binarysearch editdistance hashmap intervaldp knapsack monotone oodesign presum rectangle redo review rotatelist series sql treetraversal unionfind

Recent Posts

  • a
  • a
  • a
  • a
  • a

Recent Comments

    Archives

    Categories

    • Amusing
    • Basic
    • Easy
    • Hard
    • Life
    • Medium
    • Resource
    • Review
    • Series
    • Uncategorized
    Proudly powered by WordPress | Theme: petals by Aurorum.