# SQL Interview Questions – Team Scores in Football Tournament

### 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.

## 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

Rating: 1 out of 5.

Posted in SQL