I came up with a solution to the below scenario that generated the correct results with the test data, but when it was graded it only got 36% correct when using different data. Someone else asked for the solution to this problem here (How do i crack this SQL Soccer Matches assignment?) and I found Strange Coder's solution to be similar to mine. This solution got a 100%. What is the difference between them?

Set Up

You are given two tables, teams and matches, with the following structures:

  create table teams (
  team_id integer not null,
  team_name varchar(30) not null,
  unique(team_id)
 );


 create table matches (
  match_id integer not null,
  host_team integer not null,
  guest_team integer not null,
  host_goals integer not null,
  guest_goals integer not null,
  unique(match_id)
);

Each record in the table teams represents a single soccer team. Each record in the table matches represents a finished match between two teams. Teams (host_team, guest_team) are represented by their IDs in the teams table (team_id). No team plays a match against itself. You know the result of each match (that is, the number of goals scored by each team).

You would like to compute the total number of points each team has scored after all the matches described in the table. The scoring rules are as follows:

• If a team wins a match (scores strictly more goals than the other team), it receives three points.
• If a team draws a match (scores exactly the same number of goals as the opponent), it receives one point.
• If a team loses a match (scores fewer goals than the opponent), it receives no points.

Write an SQL query that returns a ranking of all teams (team_id) described in the table teams. For each team you should provide its name and the number of points it received after all described matches (num_points). The table should be ordered by num_points (in decreasing order). In case of a tie, order the rows by team_id (in increasing order).

For example, for:

teams:

matches:

your query should return:

My Solution

    SELECT t.team_id, t.team_name, COALESCE(SUM(num_points), 0) AS num_points
    FROM(
         SELECT t.team_id, t.team_name,
          (CASE WHEN m.host_goals > m.guest_goals THEN 3
                WHEN m.host_goals = m.guest_goals THEN 1
                WHEN m.host_goals < m.guest_goals THEN 0
                END) AS num_points
         FROM teams t
         JOIN matches m
         ON t.team_id = m.host_team
         UNION
         SELECT t.team_id, t.team_name,
          (CASE WHEN m.guest_goals > m.host_goals THEN 3
                WHEN m.guest_goals = m.host_goals THEN 1
                WHEN m.guest_goals < m.host_goals THEN 0
                END) AS num_points
         FROM teams t
         JOIN matches m
         ON t.team_id = m.guest_team
    ) AS c
    RIGHT JOIN teams t
    ON t.team_id = c.team_id
    GROUP BY t.team_id, t.team_name
    ORDER BY COALESCE(SUM(num_points), 0) DESC, t.team_id

Strange Coder's Solution

How do i crack this SQL Soccer Matches assignment?

From Strange Coder

    select team_id, team_name, 
     coalesce(sum(case when team_id = host_team then 
                   (
                    case when host_goals > guest_goals then 3
                    when host_goals = guest_goals then 1
                    when host_goals < guest_goals then 0
                    end
                   ) 
                   when team_id = guest_team then
                   (
                   case when guest_goals > host_goals then 3
                   when guest_goals = host_goals then 1
                   when guest_goals < host_goals then 0
                   end
                   )
                   end), 0) as num_points
    from Teams
    left join Matches
    on 
    Teams.team_id = Matches.host_team
    or Teams.team_id = Matches.guest_team
    group by team_id, team_name
    order by num_points desc, team_id;

CodePudding user response：

I have figured it out. I should have used UNION ALL instead of UNION.

CodePudding user response：

Alternative solution, can simply unpivot your results with CROSS APPLY instead of using UNION. Also no need to calculate ties in your CASE statement as your simply going to SUM() the results and 0 won't affect it.

Calculate Total Points per Team

DROP TABLE IF EXISTS #Team
DROP TABLE IF EXISTS #Match

CREATE TABLE #Team (team_id INT, team_name VARCHAR(100))
INSERT INTO #Team VALUES (10,'Give'),(20,'Never'),(30,'You'),(40,'Up'),(50,'Gonna')

CREATE TABLE #Match (match_id INT,host_team INT,guest_team INT,host_goals INT,guest_goals INT)
INSERT INTO #Match VALUES
(1,30,20,1,0)
,(2,10,20,1,2)
,(3,20,50,2,2)
,(4,10,30,1,0)
,(5,30,50,0,1)

;WITH cte_TotalPoints AS 
    (
    SELECT C.team_id,SUM(C.Points) AS TotalPoints
    FROM #Match AS A
    CROSS APPLY (
        SELECT host_points = CASE 
                                WHEN A.host_goals > A.guest_goals THEN 3
                                WHEN A.host_goals = A.guest_goals THEN 1
                            END
            ,guest_points = CASE 
                                WHEN A.guest_goals > A.host_goals THEN 3
                                WHEN A.host_goals = A.guest_goals THEN 1
                            END
    ) AS B
    CROSS APPLY (
        VALUES 
         (host_team,host_points)
        ,(guest_team,guest_points)
    ) AS C(team_id,points)
    GROUP BY c.team_id
)

SELECT A.team_id
    ,A.team_name
    ,TotalPoints = ISNULL(TotalPoints,0)
FROM #Team AS A
LEFT JOIN cte_TotalPoints AS B
    ON A.team_id = B.team_id