9. Team Scores in Football Tournament

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.

Problem Definition

You have 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.

Example

1706034090571475 Image scaled to 90%

Output

1706034132914458 Image scaled to 50%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

To compute the scores of all teams after all matches, we need to determine the number of points each team has earned based on their performance in each match. Points are awarded as follows:

Three points for a win (scoring more goals than the opponent).
One point for a draw (scoring the same number of goals as the opponent).
No points for a loss (scoring fewer goals than the opponent).

The solution involves joining the Teams and Matches tables, calculating points per match for each team, aggregating these points, and finally ordering the results as specified.

Approach Overview

Join Teams with Matches: Combine the Teams and Matches tables to associate each team with their respective matches, whether they were the host or the guest team.
Calculate Points per Match: Use a CASE statement to assign points to each team based on the outcome of each match (win, draw, loss).
Aggregate Points per Team: Sum the points each team has earned across all matches.
Order the Results: Sort the final output by num_points in descending order. In case of a tie, sort by team_id in ascending order.

SQL Query


SELECT team_id,
       team_name,
       SUM(CASE
             WHEN team_id = host_team
                  AND host_goals > guest_goals THEN 3
             WHEN team_id = guest_team
                  AND guest_goals > host_goals THEN 3
             WHEN (team_id = host_team OR team_id = guest_team)
                  AND host_goals = guest_goals THEN 1
             ELSE 0
           END) AS num_points
FROM   Teams T
       LEFT JOIN Matches M
              ON T.team_id = M.host_team
                  OR T.team_id = M.guest_team
GROUP  BY team_id, team_name
ORDER  BY num_points DESC, team_id ASC;

Step-by-Step Approach

Step 1: Join Teams with Matches

Associate each team with all the matches they have participated in, whether as the host or guest team. This allows us to evaluate each team’s performance in every match.

SQL Query:


SELECT T.team_id, T.team_name, M.host_team, M.guest_team, M.host_goals, M.guest_goals
FROM Teams T
LEFT JOIN Matches M
       ON T.team_id = M.host_team
           OR T.team_id = M.guest_team;

Explanation:

SELECT T.team_id, T.team_name, M.host_team, M.guest_team, M.host_goals, M.guest_goals:
- Retrieves the team ID and name from the Teams table and the corresponding match details from the Matches table.
LEFT JOIN Matches M ON T.team_id = M.host_team OR T.team_id = M.guest_team:
- Performs a left join to include all teams, whether they have participated in matches or not.
- Associates each team with matches where they are either the host or guest team.

Output After Step 1:


+---------+---------------+-----------+------------+-------------+-------------+
| team_id | team_name     | host_team | guest_team | host_goals  | guest_goals |
+---------+---------------+-----------+------------+-------------+-------------+
| 10      | DesignGuru FC | 10        | 20         | 3           | 0           |
| 10      | DesignGuru FC | 30        | 10         | 2           | 2           |
| 10      | DesignGuru FC | 10        | 50         | 5           | 1           |
| 20      | NewYork FC    | 20        | 30         | 1           | 0           |
| 20      | NewYork FC    | 20        | 30         | 1           | 0           |
| 30      | Atlanta FC    | 30        | 10         | 2           | 2           |
| 30      | Atlanta FC    | 20        | 30         | 1           | 0           |
| 30      | Atlanta FC    | 50        | 30         | 1           | 0           |
| 40      | Chicago FC    | NULL      | NULL       | NULL        | NULL        |
| 50      | Toranto FC    | 50        | 30         | 1           | 0           |
| 50      | Toranto FC    | 10        | 50         | 5           | 1           |
+---------+---------------+-----------+------------+-------------+-------------+

Step 2: Calculate Points per Match

Assign points to each team based on the outcome of each match they participated in. The CASE statement evaluates whether the team won, drew, or lost the match and assigns points accordingly.

SQL Query:


SELECT T.team_id,
       T.team_name,
       CASE
           WHEN T.team_id = M.host_team AND M.host_goals > M.guest_goals THEN 3
           WHEN T.team_id = M.guest_team AND M.guest_goals > M.host_goals THEN 3
           WHEN (T.team_id = M.host_team OR T.team_id = M.guest_team) AND M.host_goals = M.guest_goals THEN 1
           ELSE 0
       END AS points
FROM Teams T
LEFT JOIN Matches M
       ON T.team_id = M.host_team
           OR T.team_id = M.guest_team;

Explanation:

CASE Statement:
- WHEN T.team_id = M.host_team AND M.host_goals > M.guest_goals THEN 3:
  - If the team is the host and scores more goals than the guest, they win and earn 3 points.
- WHEN T.team_id = M.guest_team AND M.guest_goals > M.host_goals THEN 3:
  - If the team is the guest and scores more goals than the host, they win and earn 3 points.
- WHEN (T.team_id = M.host_team OR T.team_id = M.guest_team) AND M.host_goals = M.guest_goals THEN 1:
  - If the team is either host or guest and the match is a draw (same number of goals), they earn 1 point.
- ELSE 0:
  - In all other cases (i.e., the team loses the match), they earn 0 points.
AS points:
- Aliases the calculated points for clarity.

Output After Step 2:


+---------+---------------+--------+
| team_id | team_name     | points |
+---------+---------------+--------+
| 10      | DesignGuru FC | 3      |
| 10      | DesignGuru FC | 1      |
| 10      | DesignGuru FC | 3      |
| 20      | NewYork FC    | 3      |
| 20      | NewYork FC    | 0      |
| 30      | Atlanta FC    | 0      |
| 30      | Atlanta FC    | 0      |
| 30      | Atlanta FC    | 1      |
| 40      | Chicago FC    | 0      |
| 50      | Toranto FC    | 3      |
| 50      | Toranto FC    | 0      |
+---------+---------------+--------+

Step 3: Aggregate Points per Team

Sum the points each team has earned across all their matches to determine their total score in the tournament.

SQL Query:


SELECT team_id,
       team_name,
       SUM(CASE
             WHEN team_id = host_team
                  AND host_goals > guest_goals THEN 3
             WHEN team_id = guest_team
                  AND guest_goals > host_goals THEN 3
             WHEN (team_id = host_team OR team_id = guest_team)
                  AND host_goals = guest_goals THEN 1
             ELSE 0
           END) AS num_points
FROM   Teams T
       LEFT JOIN Matches M
              ON T.team_id = M.host_team
                  OR T.team_id = M.guest_team
GROUP  BY team_id, team_name;

Explanation:

SUM(CASE ... END) AS num_points:
- Sums the points each team has earned from all matches.
GROUP BY team_id, team_name:
- Groups the results by each team to aggregate their points.

Output After Step 3:


+---------+---------------+------------+
| team_id | team_name     | num_points |
+---------+---------------+------------+
| 10      | DesignGuru FC | 7          |
| 20      | NewYork FC    | 3          |
| 50      | Toranto FC    | 3          |
| 30      | Atlanta FC    | 1          |
| 40      | Chicago FC    | 0          |
+---------+---------------+------------+

Step 4: Order the Results

Sort the teams based on their total points in descending order. In case of a tie in points, sort the teams by their team_id in ascending order to maintain consistency.

SQL Query:


SELECT team_id,
       team_name,
       SUM(CASE
             WHEN team_id = host_team
                  AND host_goals > guest_goals THEN 3
             WHEN team_id = guest_team
                  AND guest_goals > host_goals THEN 3
             WHEN (team_id = host_team OR team_id = guest_team)
                  AND host_goals = guest_goals THEN 1
             ELSE 0
           END) AS num_points
FROM   Teams T
       LEFT JOIN Matches M
              ON T.team_id = M.host_team
                  OR T.team_id = M.guest_team
GROUP  BY team_id, team_name
ORDER  BY num_points DESC, team_id ASC;

Explanation:

ORDER BY num_points DESC, team_id ASC:
- num_points DESC:
  - Sorts the teams by their total points in descending order, so teams with higher points appear first.
- team_id ASC:
  - In case two or more teams have the same number of points, they are sorted by their team_id in ascending order to break the tie.

Final Output:


+---------+---------------+------------+
| team_id | team_name     | num_points |
+---------+---------------+------------+
| 10      | DesignGuru FC | 7          |
| 20      | NewYork FC    | 3          |
| 50      | Toranto FC    | 3          |
| 30      | Atlanta FC    | 1          |
| 40      | Chicago FC    | 0          |
+---------+---------------+------------+