Description

Table: Teams

Column NameType
team_idint
team_namevarchar
  • team_id is the column with unique values of this table.
  • Each row of this table represents a single football team.

Table: Matches

Column NameType
match_idint
host_teamint
guest_teamint
host_goalsint
guest_goalsint
  • match_id is the column of unique values 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 (i.e., Scored more goals than the opponent team).
  • A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).
  • A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).

Problem Statement

Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.

Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.

The result format is in the following example.

Example 1:

Input:

  • Teams table:
team_idteam_name
10Leetcode FC
20NewYork FC
30Atlanta FC
40Chicago FC
50Toronto FC
  • Matches table:
match_idhost_teamguest_teamhost_goalsguest_goals
1102030
2301022
3105051
4203010
5503010

Output:

team_idteam_namenum_points
10Leetcode FC7
20NewYork FC3
50Toronto FC3
30Atlanta FC1
40Chicago FC0

Solution

First you need to find points for each team based on Matches results. For this, you need to join Matches with Teams table. You need to report for each team, so you need to perform LEFT JOIN.

  1. Calculate host_points for each team.
 1WITH host_points AS (
 2    SELECT team_id, team_name,
 3        CASE WHEN host_goals > guest_goals THEN 3
 4            WHEN host_goals = guest_goals THEN 1
 5            ELSE 0 
 6        END AS num_points
 7        FROM Teams t
 8        LEFT OUTER JOIN Matches m
 9        ON t.team_id = m.host_team
10) SELECT * FROM host_points;
  1. Similarly, calculate the guest_points for each guest team.
 1WITH guest_points AS (
 2    SELECT guest_team AS team_id, team_name,
 3    CASE WHEN host_goals < guest_goals THEN 3
 4        WHEN host_goals = guest_goals THEN 1
 5        ELSE 0 END
 6    AS num_points
 7    FROM Teams t
 8    JOIN Matches m
 9    ON t.team_id = m.guest_team
10) SELECT * FROM guest_points;
  1. Now, you need to combine both of these points table into single table. You can union them to find points for each team.
1WITH unioned_points AS (
2    SELECT team_id, team_name, num_points
3        FROM host_points
4        UNION ALL
5        SELECT team_id, team_name, num_points
6            FROM guest_points
7) SELECT * FROM unioned_points;
  1. Now, you simply need to perform sum of points for each team. This is where you can perform aggregation SUM on team_id and team_name columns. You also need to order the results by num_points descending and team_id ascending.
1SELECT team_id, team_name, SUM(num_points) AS num_points
2    FROM unioned_points
3    GROUP BY team_id, team_name
4    ORDER BY num_points DESC, team_id;

The final solution looks like this.

 1WITH host_points AS (
 2    SELECT team_id, team_name,
 3        CASE WHEN host_goals > guest_goals THEN 3
 4            WHEN host_goals = guest_goals THEN 1
 5            ELSE 0 
 6        END AS num_points
 7        FROM Teams t
 8        LEFT OUTER JOIN Matches m
 9        ON t.team_id = m.host_team
10), guest_points AS (
11    SELECT guest_team AS team_id, team_name,
12    CASE WHEN host_goals < guest_goals THEN 3
13        WHEN host_goals = guest_goals THEN 1
14        ELSE 0 END
15    AS num_points
16    FROM Teams t
17    JOIN Matches m
18    ON t.team_id = m.guest_team
19), unioned_points AS (
20    SELECT team_id, team_name, num_points
21        FROM host_points
22        UNION ALL
23        SELECT team_id, team_name, num_points
24            FROM guest_points
25) SELECT team_id, team_name, SUM(num_points) AS num_points
26    FROM unioned_points
27    GROUP BY team_id, team_name
28    ORDER BY num_points DESC, team_id;