Competitive Programming: CodeSignal – footballGroupStatictics (A World Cup SQL Challenge)

Description

You are creating a website that will help you and your friends keep track of the results of soccer games from all around the world. You store all results of one group in a table, results. You want to sort the teams in a complex way – first by points, then by total goal differences, and then by total goals. If all of these parameters are equal, sort the teams alphabetically.

The results table contains the following columns:

• first_team – the name of the first team;
• second_team – the name of the second team;
• first_team_score – the number of goals scored by the first team;
• second_team_score – the number of goals scored by the second team.

Here the primary key is the pair (first_team, second_team). Return the list of team names sorted in the way described above.

Note: see three points for a win to understand how points are calculated.

Example

For given table results

the output should be

• Preachers have 6 points (from winning two games), 2 is the total goal difference (2 - 1 = 1 from the first game, and 3 - 2 = 1 from the second game), and the total goal count is 5 (2 from the first game, plus 3 from the second game).
• Razorbacks have 1 point (from their draw game with the Blackflies), a goal difference of -1 (2 - 3 = -1 from their first game, and 1 - 1 = 0 from their second game), and a total goal count of 3.
• Blackflies also have 1 point and -1 goal difference, but their total goal count is 2.

Solution below . . .

Solution

Create a function getPoints to take the team score and the opponent score and return the points earned — 3, 1 or 0.

Create a temporary table by transforming each row in the original table to two rows containing team name, goals, differential and points. From the example table:

Then group the rows by team name, sum the numeric data and sort in the prescribed order:

Here’s the SQL code:

CREATE PROCEDURE footballGroupStatistics()
BEGIN
    CREATE TEMPORARY TABLE T1
	SELECT first_team AS team_name, 
        first_team_score AS goals,
        first_team_score - second_team_score AS diff,
        getPoints(first_team_score, second_team_score) AS points
    FROM results
    UNION ALL
	SELECT second_team AS team_name, 
        second_team_score AS goals,
        second_team_score - first_team_score AS diff,
        getPoints(second_team_score, first_team_score) AS points
    FROM results;

    CREATE TEMPORARY TABLE T2
    SELECT team_name, 
        SUM(goals) AS goals,
        SUM(diff) AS diff,
        SUM(points) AS points
    FROM T1
    GROUP BY team_name
    ORDER BY points desc, diff desc, goals desc, team_name;

    SELECT team_name FROM T2;

    DROP TEMPORARY TABLE T1;
    DROP TEMPORARY TABLE T2;
END;

CREATE FUNCTION getPoints(us INT, them INT)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE result INT;

    CASE
        WHEN us > them THEN SET result = 3;
        WHEN us = them THEN SET result = 1;
        ELSE SET result = 0;
    END CASE;

    RETURN result;
END