Problem

Table: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id is the column with unique values 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 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).

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Input: 
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            |
+------------+--------------+---------------+-------------+--------------+
Output: 
+------------+--------------+---------------+
| 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

Approach

We need to calculate the total points for each team based on match results. For each match, the host and guest teams get points according to the result (win: 3, draw: 1, loss: 0). We aggregate the points for each team and join with the Teams table to get the team names.

Code

MySQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT t.team_id, t.team_name, COALESCE(SUM(s.points), 0) AS num_points
FROM Teams t
LEFT JOIN (
  SELECT host_team AS team_id,
         CASE
           WHEN host_goals > guest_goals THEN 3
           WHEN host_goals = guest_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
  UNION ALL
  SELECT guest_team AS team_id,
         CASE
           WHEN guest_goals > host_goals THEN 3
           WHEN guest_goals = host_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
) s ON t.team_id = s.team_id
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id ASC;
Oracle
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT t.team_id, t.team_name, NVL(SUM(s.points), 0) AS num_points
FROM Teams t
LEFT JOIN (
  SELECT host_team AS team_id,
         CASE
           WHEN host_goals > guest_goals THEN 3
           WHEN host_goals = guest_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
  UNION ALL
  SELECT guest_team AS team_id,
         CASE
           WHEN guest_goals > host_goals THEN 3
           WHEN guest_goals = host_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
) s ON t.team_id = s.team_id
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id ASC;
PostgreSQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT t.team_id, t.team_name, COALESCE(SUM(s.points), 0) AS num_points
FROM Teams t
LEFT JOIN (
  SELECT host_team AS team_id,
         CASE
           WHEN host_goals > guest_goals THEN 3
           WHEN host_goals = guest_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
  UNION ALL
  SELECT guest_team AS team_id,
         CASE
           WHEN guest_goals > host_goals THEN 3
           WHEN guest_goals = host_goals THEN 1
           ELSE 0
         END AS points
  FROM Matches
) s ON t.team_id = s.team_id
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id ASC;

Explanation

We use a UNION ALL to collect points for both host and guest teams for each match, then sum the points for each team. Teams with no matches get 0 points. The result is ordered by points descending, then team_id ascending.

Complexity

  • ⏰ Time complexity: O(M + N) where M is the number of teams and N is the number of matches.
  • 🧺 Space complexity: O(M) for storing the points per team.