PostgreSQL | Codility | SqlWorldCup

생각했던 것 보다 시간을 많이 사용했던 문제

직관적이고 간결한 쿼리를 짜고자 고민했고, null 처리에서 약간 시간을 잡아먹었다.

 

그럼 바로 문제.

Question

Given a list of matches in a group stage of the soccer World Cup, compute the number of points each team currently has.

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:

   team_id | team_name
  ---------+---------------
   10      | Give
   20      | Never
   30      | You
   40      | Up
   50      | Gonna


  matches:

   match_id | host_team | guest_team | host_goals | guest_goals
  ----------+-----------+------------+------------+-------------
   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
your query should return:

   team_id | team_name | num_points
  ---------+-----------+------------
   20      | Never     | 4
   50      | Gonna     | 4
   10      | Give      | 3
   30      | You       | 3
   40      | Up        | 0
Copyright 2009–2023 by Codility Limited. All Rights Reserved. Unauthorized copying, publication or disclosure prohibited.

매치 테이블에 호스트와 게스트 팀의 team_id가 참조값으로 들어가며 호스트와 게스트의 골 수가 기록된다.
두 팀 중 승리한 팀은 3포인트를 가져가고, 동점이면 두 팀 모두 1점 씩 가져간다. 패할 경우는 0점이다.
이 때, 각 팀 별 최총 획득 포인트를 보고자한다. 최종 획득 포인트를 내림차순, team_id를 오름차순으로 정렬하여 결과값을 도출한다.

 

Solve

with temp as(
    with points as (
        SELECT *
            , case 
                    when host_goals > guest_goals then 3
                    when host_goals = guest_goals then 1
                    else 0 
                end as host_points
            , case 
                    when guest_goals > host_goals then 3
                    when guest_goals = host_goals then 1
                    else 0 
                end as guest_points      
        FROM matches)
    SELECT host_team as team
         , sum(host_points) as points
    FROM points
    GROUP BY host_team
    UNION ALL
    SELECT guest_team as team
         , sum(guest_points) as points
    FROM points
    GROUP BY guest_team)
            

SELECT t.team_id
     , t.team_name
     , coalesce(sum(p.points),0) as point
FROM teams as t
    LEFT JOIN temp as p ON t.team_id = p.team 
GROUP BY t.team_id, t.team_name
ORDER BY point desc, team_id asc

• case 문을 활용하여 매치별 호스트팀의 포인트, 게스트팀의 포인트 필드가 추가된 테이블 값을 만들었다.
• 해당 테이블 값을 with 문으로 감싸 host에 대한 총 합계, guest에 대한 총 합계를 구할 수 있도록 활용했다
• host 합계 테이블 값과 guest 합계 테이블 값을 union all 로 묶어 보고자하는 team_id 별 합계 값을 가진 간략한 테이블 값을 만들었고, 최종 참조 테이블로 활용하기 위해 한 번 더 with 문으로 감싸 편리하게 호출할 수 있도록 했다.
• teams 테이블을 기준으로 Left join을 하여 포인트가 없는 팀도 출력될 수 있도록 했다.
• 이 때, 포인트가 없는 팀의 포인트 값은 null로 표현되므로 coalesce 함수를 활용했다.
  • PostgreSQL은 ifnull(), isnull()을 지원하지 않는다. 

---

아래는 고민의 흔적들

with a as(
            with temp as (
            SELECT *
                , case 
                        when host_goals > guest_goals then 3
                        when host_goals = guest_goals then 1
                        else 0 
                    end as host_point
                , case 
                        when guest_goals > host_goals then 3
                        when guest_goals = host_goals then 1
                        else 0 
                    end as guest_point       
            FROM matches)

            SELECT host_team
                , sum(host_point) as total_host_point
            FROM temp
            GROUP BY host_team )

    , b as( 
            with temp as (
                SELECT *
                    , case 
                            when host_goals > guest_goals then 3
                            when host_goals = guest_goals then 1
                            else 0 
                        end as host_point
                    , case 
                            when guest_goals > host_goals then 3
                            when guest_goals = host_goals then 1
                            else 0 
                        end as guest_point       
                FROM matches)
                
            SELECT guest_team
                , sum(guest_point) as total_guest_point
            FROM temp
            GROUP BY guest_team )

SELECT team_id
     , team_name
     , a.total_host_point + b.total_guest_point as num_points
FROM teams 
    JOIN a ON teams.team_id = a.host_team 
    JOIN b ON teams.team_id = b.guest_team

• 완성 쿼리는 아니지만 처음 접근하면서 여러개의 with 문을 활용하고, 최종 쿼리에서 책정한 point를 합산하고자 했다.
• 중복된 코드가 길어져서 union all을 활용했다.
• rollup, cube 함수를 이용하면 될까 고민도 했는데, 집계값만 간결하게 이용하고자 union all 을 활용했고 해당 방법이 맞았다고 생각한다.

---

Source

https://app.codility.com/programmers/trainings/6/sql_world_cup/

SqlWorldCup coding task - Practice Coding - Codility