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.

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