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

Team Scores in Football Tournament LeetCode Solution

Problem Statement

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

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:

Output:

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;