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

List of all LeetCode Solution

Team Scores in Football Tournament LeetCode Solution

Problem Statement

Column Name	Type
team_id	int
team_name	varchar

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

Column Name	Type
match_id	int
host_team	int
guest_team	int
host_goals	int
guest_goals	int

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_id	team_name
10	Leetcode FC
20	NewYork FC
30	Atlanta FC
40	Chicago FC
50	Toronto FC

match_id	host_team	guest_team	host_goals	guest_goals
1	10	20	3	0
2	30	10	2	2
3	10	50	5	1
4	20	30	1	0
5	50	30	1	0

<strong>Output:</strong>Code language: HTML, XML (xml)

team_id	team_name	num_points
10	Leetcode FC	7
20	NewYork FC	3
50	Toronto FC	3
30	Atlanta FC	1
40	Chicago FC	0

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;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)