[Codility] - SqlWorldCup

솔찍히 이건 잘 몰랐고 한참 헤매다가 null값 처리를 해주는 함수 'coalesce' 이놈을 좀 써야한다는 걸 늦게나마 알았다... 다른 코테에서 많이 나오고 자주 쓰는 함수라고 하니 알아두쟈.. 

---

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

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:

your query should return:

---

쉽게말해 축구 팀의 승점을 계산하고 나열하라 이건데, 이게 나름 쉬워보이는데 고민하게 만들었음.. 

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 A
LEFT JOIN matches B
ON 
A.team_id = B.host_team OR A.team_id = B.guest_team
GROUP BY team_id, team_name
ORDER BY num_points DESC, team_id

case when을 사용해서 승점 계산을 하고, 양쪽으로 만들어서 두개의 테이블이 조인걸리게끔.. 그리고 그놈의 coalesce를 사용해 뒷쪽의 0 (null) 값을 처리해주는 것 

문제가 많이 없지만, 은근히 까다로움 .. 문제 더 다양하게 나와줬으면 합니다. ㅎ