[Codility] SqlWorldCup - SQL(PostgreSQL)

코딜리티에는 SQL 문제가 많이 없는 대신 프로그래머스보다 복잡하고 여러 함수를 사용하도록 문제가 구성되어 있는 것 같다. 이 문제도 답변이 많이 없는 것 같아 정리해 두기로 했다.

 

 

---

 

🤔 문제

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

SqlWorldCup coding task - Practice Coding - Codility

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

코딜리티의 Exercises 6 > SQL 문제이고, Mdeium단계이다.

 

😄 나의 풀이

-- write your code in PostgreSQL 9.4
with win_table as (
    select case when host_goals > guest_goals then host_team
                when host_goals < guest_goals then guest_team
        end as team_id,
        3 as points
    from matches
    where host_goals != guest_goals
),
draw_table as (
    select host_team  as team_id, 1 as points from matches where host_goals = guest_goals
    union all
    select guest_team  as team_id, 1 as points from matches where host_goals = guest_goals
),
points_table as (
    select * from win_table
    union all
    select * from draw_table
)

select teams.team_id, teams.team_name, coalesce(points_sum_table.num_points,0) as num_points
from teams
left join(
    select team_id, sum(points) as num_points
    from points_table
    group by team_id
) points_sum_table on teams.team_id = points_sum_table.team_id
order by num_points desc, teams.team_id

with를 사용하여 우승자 테이블(win_table), 동점자 테이블(draw_table)을 생성하고 두 테이블의 점수를 전부 모아 points_table을 생성한다. 이때 case when과 union all 을 사용하는 것이 핵심 !

그리고 team_id와 team_name이 있는 teams 테이블에 left join하여 팀별 최종 점수를 취합한다.

처음에 문제 속 order by 조건을 놓쳐서 에러가 났었다. 문제를 꼼꼼히 읽자 😅