Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1212.html
1212. Team Scores in Football Tournament
Level
Medium
Description
Table: Teams
+---------------+----------+
| 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.
Table: Matches
+---------------+---------+
| 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 query result format is in the following example:
Teams table:
+-----------+--------------+
| team_id | team_name |
+-----------+--------------+
| 10 | Leetcode FC |
| 20 | NewYork FC |
| 30 | Atlanta FC |
| 40 | Chicago FC |
| 50 | Toronto FC |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| 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 |
+------------+--------------+---------------+-------------+--------------+
Result table:
+------------+--------------+---------------+
| 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 |
+------------+--------------+---------------+
Solution
For each team in table Teams, obtain the entries from table Matches and calculate the sum of scores accordingly. If an entry in table Matches have the current team as host_team or guest_team, then the current team may get three points, one point or no points. Otherwise, the team has ho points for the entry.
Finally, sort the results according to num_points in descending order and then according to team_id in ascending order.
# Write your MySQL query statement below
select team_id, team_name, num_points from (
select Teams.team_id, Teams.team_name, sum(
case
when Teams.team_id = Matches.host_team then
case
when Matches.host_goals > Matches.guest_goals then 3
when Matches.host_goals = Matches.guest_goals then 1
else 0
end
when Teams.team_id = Matches.guest_team then
case
when Matches.host_goals < Matches.guest_goals then 3
when Matches.host_goals = Matches.guest_goals then 1
else 0
end
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
) result
order by result.num_points desc, result.team_id;