Problem Description –
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 an SQL query 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 query result format is in the following example.

Difficulty Level – Medium
Problem Link – Football Tournament
Solution –
WITH result as (
SELECT *,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
WHEN host_goals < guest_goals THEN 0
END as host_points,
CASE WHEN guest_goals > host_goals THEN 3
WHEN guest_goals = host_goals THEN 1
WHEN guest_goals < host_goals THEN 0
END as guest_points
FROM Matches
)
SELECT
t.team_id,
t.team_name,
COALESCE(y.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(host_points) as num_points
FROM result
GROUP BY 1
UNION ALL
SELECT
guest_team as team_id,
SUM(guest_points) as num_points
FROM result
GROUP BY 1
) x
GROUP BY 1
ORDER BY 1
) y
ON t.team_id = y.team_id
ORDER BY 3 DESC, 1 ASC
To solve this problem first we calculate the points for the host and guest teams.
WITH result as (
SELECT *,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
WHEN host_goals < guest_goals THEN 0
END as host_points,
CASE WHEN guest_goals > host_goals THEN 3
WHEN guest_goals = host_goals THEN 1
WHEN guest_goals < host_goals THEN 0
END as guest_points
FROM Matches
)
SELECT * FROM result

Next we group the data and add the total points for the host and guest teams then combine both the results.
WITH result as (
SELECT *,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
WHEN host_goals < guest_goals THEN 0
END as host_points,
CASE WHEN guest_goals > host_goals THEN 3
WHEN guest_goals = host_goals THEN 1
WHEN guest_goals < host_goals THEN 0
END as guest_points
FROM Matches
)
SELECT
host_team as team_id,
SUM(host_points) as num_points
FROM result
GROUP BY 1
UNION ALL
SELECT
guest_team as team_id,
SUM(guest_points) as num_points
FROM result
GROUP BY 1

Now, we again group the data and calculate the total points for each teams.
WITH result as (
SELECT *,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
WHEN host_goals < guest_goals THEN 0
END as host_points,
CASE WHEN guest_goals > host_goals THEN 3
WHEN guest_goals = host_goals THEN 1
WHEN guest_goals < host_goals THEN 0
END as guest_points
FROM Matches
)
SELECT
team_id,
SUM(num_points) as num_points
FROM (
SELECT
host_team as team_id,
SUM(host_points) as num_points
FROM result
GROUP BY 1
UNION ALL
SELECT
guest_team as team_id,
SUM(guest_points) as num_points
FROM result
GROUP BY 1
) x
GROUP BY 1
ORDER BY 1

We almost have the result. All we need to do is combine this table with the Teams table using a left join. And if a team does not played any matches then replace the null points with zeros.
WITH result as (
SELECT *,
CASE WHEN host_goals > guest_goals THEN 3
WHEN host_goals = guest_goals THEN 1
WHEN host_goals < guest_goals THEN 0
END as host_points,
CASE WHEN guest_goals > host_goals THEN 3
WHEN guest_goals = host_goals THEN 1
WHEN guest_goals < host_goals THEN 0
END as guest_points
FROM Matches
)
SELECT
t.team_id,
t.team_name,
COALESCE(y.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(host_points) as num_points
FROM result
GROUP BY 1
UNION ALL
SELECT
guest_team as team_id,
SUM(guest_points) as num_points
FROM result
GROUP BY 1
) x
GROUP BY 1
ORDER BY 1
) y
ON t.team_id = y.team_id
ORDER BY 3 DESC, 1 ASC
