Team Scores in Football Tournament LeetCode Solution

Last updated on October 9th, 2024 at 10:14 pm

This Leetcode problem Team Scores in Football Tournament LeetCode Solution is done in SQL.

List of all LeetCode Solution

Level of Question

Medium

Team Scores in Football Tournament LeetCode Solution

Team Scores in Football Tournament LeetCode Solution

Problem Statement

Column NameType
team_idint
team_namevarchar
Table: Teams

team_id is the primary key of this table.
Each row of this table represents a single football team.

Column NameType
match_idint
host_teamint
guest_teamint
host_goalsint
guest_goalsint
Table: Matches

match_id is the primary key of this table.
Each row is a record of a finished match between two different teams.
Teams host_team and guest_team are represented by their IDs in the teams table (team_id) and they scored host_goals and guest_goals goals respectively.

You would like to compute the scores of all teams after all matches. Points are awarded as follows:
A team receives three points if they win a match (Score strictly more goals than the opponent team).
A team receives one point if they draw a match (Same number of goals as the opponent team).
A team receives no points if they lose a match (Score less goals than the opponent team).

Write an SQL query that selects the team_id, team_name and num_points of each team in the tournament after all described matches. Result table should be ordered by num_points (decreasing order). In case of a tie, order the records by team_id (increasing order).

The result format is in the following example.

Example 1:
Input:

team_idteam_name
10Leetcode FC
20NewYork FC
30Atlanta FC
40Chicago FC
50Toronto FC
Teams table:
match_idhost_team guest_teamhost_goalsguest_goals
1102030
2301022
3105051
4203010
5503010
Matches table:
Output:
team_idteam_name num_points
10Leetcode FC7
20NewYork FC3
50Toronto FC3
30Atlanta FC1
40Chicago FC0

1. Team Scores in Football Tournament LeetCode Solution MySQL

select 
  t.team_id, 
  team_name, 
  ifnull(num_points, 0) as num_points 
from 
  Teams as t 
  left join (
    select 
      team_id, 
      sum(num_points) as num_points 
    from 
      (
        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 u 
    group by 
      team_id
  ) as r on t.team_id = r.team_id 
order by 
  num_points desc, 
  team_id asc;

2. Team Scores in Football Tournament LeetCode Solution MySQL (Another approach)

select 
  team_id, 
  team_name, 
  sum(
    if(
      team_id = host_team, case when host_goals > guest_goals then 3 when host_goals = guest_goals then 1 else 0 end, 
      0
    )
  ) + sum(
    if(
      team_id = guest_team, case when host_goals < guest_goals then 3 when host_goals = guest_goals then 1 else 0 end, 
      0
    )
  ) as num_points 
from 
  Teams as t, 
  Matches as m 
group by 
  team_id 
order by 
  num_points desc, 
  team_id asc;
Scroll to Top