Team Scores in Football Tournament LeetCode Solution

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

List of all 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:

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

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)```
Scroll to Top