[SQL Runday] HackerRank - Interviews

이제 시작한지 2주 좀 되어가는거 같은데 벌써 해커랭크의 Intermediate skills를 다 끝냈다..! 완전 뿌듯하다.

각 공모전별로 contest_id, hacker_id, name, total_submissions의 개수, total_accepted_submissions의 개수, total_views, total_unique_views(고유 조회수?)를 조회하는 쿼리를 작성해라.

• 총 합을 구하는 4개의 컬럼이 모두 0이라면 제외해라.
• 특정 contest는 2 개 이상의 대학에서 후보자를 심사하는데 사용될 수 있지만, 각 대학은 하나의 심사 contest만 개최한다.

Table

Challenges

• challenge_id 칼럼은 사만다가 까먹은 contest_id를 갖고 있는 contest들 중 하나에 속해있는 챌린지의 id임. (뭐라냐)
• college_id는 해당 challenge가 후보자들에게 주어진 대학.
  

View_Stats

• 총 조회수 : 후보자들이 해당 챌린지를 본 조회수
• 총 고유 조회수 : 중복 제외한 고유한 후보자들이 해당 챌린지를 본 조회수. max는 모든 후보자 명수임.
  

Colleges

• college_id : 대학 id
• contest_id : 사만다가 사용한 후보자들을 심사할때 사용한 공모전 id (해당 대학이 주최한 공모전이 아님. 사만다는 특정 공모전을 2개 이상의 대학에 대한 후보자 심사할 때 사용할 수 있음.)

Working Process

• 문제 읽으면서 헷갈렸던 점이 contest 테이블에 있는 Hacker는 contest를 만든 해커임. 심사를 받는 후보자들이 아님.
• 테이블과 칼럼의 이름이 성격에 맞게 딱 떨어지지 않고, 의미가 불분명한 것도 있어서 테이블의 관계를 제대로 이해하는 것이 가장 중요한 것 같다. 사실 좀 꼬아서 어렵게 생각했는데 explanation을 보면 그냥 쉽게 생각해도 되는거 같다..?
• 그니까 정리하자면, Input 예시를 기준으로 보자면
  대학 11219 후보 심사할때 66406 contest를 사용했다.(contest T) 대학 11219가 열은 contest에는 18765 챌린지와 47127 챌린지가 있다.(Challenges T) 따라서 각 챌린지의 조회수와 제출 횟수를 보자면
  47127 챌린지) 조회수 26 + 15
  18765 챌린지) 조회수 43 + 72
  = 총 조회수 156
  이렇게 총 제출, 받아들인 제출, 총 조회수, 고유 조회수를 구하면 된다.
• 이렇게 보면 그냥 contest-challenge-view-sub stats 테이블을 차례차례 join하면 될 거 같다.
• 그리고 view, sub을 challenge_id 기준으로 group by 해서 sum()하면 될듯.
• 라고 쉽게 생각했는데 왜 답이 아니지???

Errors

1. 쿼리 값이 NULL일때 0으로 만들어 주고 싶으면 IFNULL(sum(), 0) < 이런식으로 해주면 된다.
2. Having sum(어쩌고) = 0 << 이 구문이 계속 먹히지 않았음. 늘 alias 쓰는 걸 생활화 하자. IFNULL(sum(), 0) as sumtv << 이런식으로 지정하고 Having sumtv = 0 이렇게 조건을 걸어야 원하는 값이 나온다.

첫 시도

select ct.contest_id, ct.hacker_id, ct.name,
ifnull(sum(v.total_views), 0) as sum_tv,
ifnull(sum(v.total_unique_views),0) as sum_tuv,
ifnull(sum(s.total_submissions), 0) as sum_ts,
ifnull(sum(s.total_accepted_submissions), 0) as sum_tas
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join View_Stats v on v.challenge_id = cl.challenge_id
left join Submission_Stats s on s.challenge_id = cl.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having sum_tv + sum_tuv + sum_ts + sum_tas > 0
order by ct.contest_id

1. 문제에서 contest 기준으로 조회하라고 했기 때문에,
   contest > 를 사용해 심사한 college > 에 쓰인 challenges > 각각의 view stats, sub stats 이렇게 left join 하는 거라고 쉽게 생각했다.
   그리고 group by contest_id 해서 challenge 별 stats를 다 합쳤다.

2. 왜 에러가 나는지 찾기 위해 Rose 한 사람의 id로만 쿼리해 보았다.

에러

정답

에러에서 훨씬 뭔가 많이 더해져서 나온다. select이랑 group by에 challenge_id를 넣어서 어떻게 더해졌는지 확인해보려 했다.

-- 다음 쿼리로 실행함
select ct.contest_id, ct.hacker_id, ct.name, cl.challenges_id
ifnull(sum(v.total_views), 0) as sum_tv
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join View_Stats v on v.challenge_id = cl.challenge_id
where ct.name = 'Rose'
group by ct.contest_id, ct.hacker_id, ct.name, cl.challenges_id
order by ct.contest_id

845 579 Rose 97 0 
845 579 Rose 145 170 
845 579 Rose 276 72 
845 579 Rose 345 0 
845 579 Rose 492 124 
845 579 Rose 558 0 
845 579 Rose 773 301 
845 579 Rose 791 22 
845 579 Rose 829 186 
845 579 Rose 868 0 
845 579 Rose 1003 24 
845 579 Rose 1183 107 
845 579 Rose 1322 129 
845 579 Rose 1387 91 
845 579 Rose 1483 0 
845 579 Rose 1526 20 
845 579 Rose 1681 35 
845 579 Rose 1712 156 
845 579 Rose 1827 63 
845 579 Rose 1979 135 

total_views를 다 더하면 1635가 나오는데? 정답이 나온다. 뭐지....
그런데 'submission stats' 테이블과 함께 join 했더니
845 579 Rose 2747 3301 라는 오답이 나왔다~! 이게 문제였구만. 왜 1635에서 2747로 뻥튀기가 된 것이냐?

845 579 Rose 97 0 117 
845 579 Rose 145 170 152 
845 579 Rose 276 144 114 
845 579 Rose 345 0 139 
845 579 Rose 492 124 0 
845 579 Rose 558 0 270 
845 579 Rose 773 602 360 
845 579 Rose 791 66 207 
845 579 Rose 829 372 484 
845 579 Rose 868 0 81 
845 579 Rose 1003 48 193 
845 579 Rose 1183 107 156 
845 579 Rose 1322 516 494 
845 579 Rose 1387 91 27 
845 579 Rose 1483 0 128 
845 579 Rose 1526 20 0 
845 579 Rose 1681 70 95 
845 579 Rose 1712 156 0 
845 579 Rose 1827 126 216 
845 579 Rose 1979 135 68 

276번 챌린지가 2배, 773 2배, 791 3배, 829 2배, 1003 2배 등 뻥튀기 된 지표를 확인할 수 있다. 왜 이렇게 발생한 것일까? 그것은. 이미 sum되어 계산된 테이블에 또 한번 병합을 하고 sum 계산을 할 경우, 각 v_stat 개수 별로 따블, 쓰리블이 되는 것. 276번 챌린지의 경우 v_stat 값이 2개, sub_stat 값이 2개여서 2배가 됐을것이다.

845 579 Rose 276 33 31 
845 579 Rose 276 39 31 
845 579 Rose 276 33 26 
845 579 Rose 276 39 26 

left join만 쭉 해서 쿼리 select ct.contest_id, ct.hacker_id, ct.name, cl.challenge_id, v.total_views, s.total_submissions해서 구한 결과값을 보면 276챌린지가 33, 39 total view 값이 2번씩 호출된 것을 알 수 있다. 이는 view 값 33, 39가 sub값 31, 26과 병렬하다보니 그런 것. 따라서, 이런 중복 결과를 없애기 위해선 challenge_id sum 테이블을 view, sub 별로 각각 구해서 병합하는 것이 맞다.

최종 쿼리

select ct.contest_id, ct.hacker_id, ct.name,
ifnull(sum(ss.sum_ts), 0) as sumsum_ts,
ifnull(sum(ss.sum_tas), 0) as sumsum_tas,
ifnull(sum(vs.sum_tv), 0) as sumsum_tv,
ifnull(sum(vs.sum_tuv),0) as sumsum_tuv
from Contests ct
left join Colleges c on ct.contest_id = c.contest_id
left join Challenges cl on cl.college_id = c.college_id
left join (select challenge_id, sum(total_submissions) as sum_ts,
           sum(total_accepted_submissions) as sum_tas
            from Submission_Stats
            group by challenge_id)ss
            on cl.challenge_id = ss.challenge_id
left join (select challenge_id, sum(total_views) as sum_tv,
           sum(total_unique_views) as sum_tuv
            from View_Stats
            group by challenge_id)vs
            on cl.challenge_id = vs.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having sumsum_tv + sumsum_tuv + sumsum_ts + sumsum_tas > 0
order by ct.contest_id

challenge_id 별로 stats를 sum하여 따로 구한 테이블을 left join 해야 한다!

결론 : Sum(), Sum() 계산 1번 이상 해야할 경우, 계속 계속 join 하지말고 따로 테이블로 구해서 더한다음 메인 테이블에 병합해주자. 그 편이 쉽고 빠르다.

정리된 포스트를 발행하고 한번 쭉 읽어보니 나름 어려운 부분이 아닌데 풀때는 왜 이렇게 막연하고 답답했는지 모르겠다. 이렇게 시행착오를 겪어가고, 내가 틀린 부분을 왜 틀렸는지 집요하게 파고 들고 계속 끊임없이 쿼리하다보면 머릿속으로 쏙쏙 할 수 있을거라 생각한다.