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.
For given table results
the output should be
6points (from winning two games),
2is the total goal difference (
2 - 1 = 1from the first game, and
3 - 2 = 1from the second game), and the total goal count is
2from the first game, plus
3from the second game).
1point (from their draw game with the
Blackflies), a goal difference of
2 - 3 = -1from their first game, and
1 - 1 = 0from their second game), and a total goal count of
-1goal difference, but their total goal count is
Solution below . . .
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