PostgreSQL/Codility SqlWorldCup

Posted on | In

[PostgreSQL/Codility] SqlWorldCup

๐Ÿ“Œ๋ฌธ์ œ๋งํฌ

matches ํ…Œ์ด๋ธ”์—” ๊ฐ ์ถ•๊ตฌ๊ฒฝ๊ธฐ์—์„œ์˜ ๊ณจ์ˆ˜๊ฐ€ ์ ํ˜€์žˆ๋‹ค.
๊ฒฝ๊ธฐ๊ฒฐ๊ณผ๊ฐ€ ๋™์ ์ธ ๊ฒฝ์šฐ, ๋‘ ํŒ€์—๊ฒŒ 1์ ์”ฉ ๋ถ€๊ณผํ•˜๊ณ  ํ•œ ํŒ€์ด ์ด๊ธด๊ฒฝ์šฐ, ์ด๊ธดํŒ€์ด๊ฒŒ 3์ ์„ ๋ถ€๊ณผํ•œ๋‹ค.

๋‚˜๋Š” with์ ˆ์„ 3๊ฐœ๋ฅผ ๋งŒ๋“ค์–ด์„œ teams ํ…Œ์ด๋ธ”๊ณผ joinํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์„ ํƒํ–ˆ๋‹ค.

  • tie1 table : ๊ฒฝ๊ธฐ๊ฒฐ๊ณผ๊ฐ€ ๋™์ ์ธ ๊ฒฝ์šฐ, host_team์— ๋” ํ•ด์•ผํ•  ์ ์ˆ˜
  • tie2 table : ๊ฒฝ๊ธฐ๊ฒฐ๊ณผ๊ฐ€ ๋™์ ์ธ ๊ฒฝ์šฐ, guest_team์— ๋” ํ•ด์•ผํ•  ์ ์ˆ˜
  • win table : ์ด๊ธด ํŒ€์ด ์žˆ๋Š” ๊ฒฝ์šฐ, ๊ฐ ํŒ€์— ๋”ํ•ด์•ผํ•  ์ ์ˆ˜

solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
with tie1 as (select F.host_team, sum(F.cnt) as num
from (select A.host_team, 1 as cnt
from (SELECT  match_id, host_team, guest_team, host_goals, guest_goals,case
        when host_goals = guest_goals then 1
        else 3
        end as score,
        case
        when host_goals > guest_goals then host_team
        when host_goals < guest_goals then guest_team
        else NULL
        end as winner
        from matches ) A
where winner is null) F -- ๊ฒฝ๊ธฐ๊ฒฐ๊ณผ๊ฐ€ ๋™์ ์ธ ๊ฒฝ์šฐ, host_team์— ๋” ํ•ด์•ผํ•  ์ ์ˆ˜ 
group by F.host_team),
tie2 as (select F.guest_team, sum(F.cnt) as num
from (select A.guest_team, 1 as cnt
from (SELECT  match_id, host_team, guest_team, host_goals, guest_goals,case
        when host_goals = guest_goals then 1
        else 3
        end as score,
        case
        when host_goals > guest_goals then host_team
        when host_goals < guest_goals then guest_team
        else NULL
        end as winner
        from matches ) A
where winner is null) F
group by F.guest_team), -- ๊ฒฝ๊ธฐ๊ฒฐ๊ณผ๊ฐ€ ๋™์ ์ธ ๊ฒฝ์šฐ, guest_team์— ๋” ํ•ด์•ผํ•  ์ ์ˆ˜ 
win as (select A.winner, sum(A.score) as num
from (SELECT  match_id, host_team, guest_team, host_goals, guest_goals,case
        when host_goals = guest_goals then 1
        else 3
        end as score,
        case
        when host_goals > guest_goals then host_team
        when host_goals < guest_goals then guest_team
        else NULL
        end as winner
from matches) A
group by A.winner) -- ์ด๊ธด ํŒ€์ด ์žˆ๋Š” ๊ฒฝ์šฐ, ๊ฐ ํŒ€์— ๋”ํ•ด์•ผํ•  ์ ์ˆ˜ 
-- teams, win, tie1, tie2 join
select R.team_id, R.team_name, R.sumv1 + R.sumv2 + R.sumv3 as num_points
from (select team_id, team_name, 
case when win.num is null then 0 else win.num end as sumv1,
case when tie1.num is null then 0 else tie1.num end as sumv2,
case when tie2.num is null then 0 else tie2.num end as sumv3
from teams
left outer join win on teams.team_id=win.winner
left outer join tie1 on teams.team_id=tie1.host_team
left outer join tie2 on teams.team_id=tie2.guest_team) R
order by num_points desc, R.team_id