[해커랭크] SQL(hard) - Interviews 문제 풀이

MS SQL Server를 사용합니다.

테이블 간 관계와 문제가 요구하는 output은 이미지와 같습니다.

유의사항

• mysql의 경우, with 구문을 사용할 수 없습니다 : 해커랭크에서는 8.0 이하 버전을 지원합니다.
  • discussion에서 많이 언급된 질문
• 컬럼 순서를 맞춰야 정답으로 인정됩니다 : contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views
  • 합계 컬럼 순서에 주의할 것
• group by - sum을 두 번 사용할 것 : 인라인 뷰와 메인쿼리에서 모두 사용해야 합니다. 이유는 하단에 기재해두었습니다!
  • discussion에서 많이 언급된 질문

 

인라인 뷰 생성(with)

with sum_view as (
    select vs.challenge_id, sum(vs.total_views) as s1, sum(vs.total_unique_views) as s2
    from View_Stats as vs
    group by vs.challenge_id
), sum_sub as (
    select ss.challenge_id, sum(ss.total_submissions) as s3, sum(ss.total_accepted_submissions) as s4
    from Submission_Stats as ss
    group by ss.challenge_id
), cte as (
    select c.challenge_id, a.contest_id, a.hacker_id, a.name
    from Contests a left join Colleges b
    on a.contest_id = b.contest_id
    left join Challenges c 
    on b.college_id = c.college_id
)

• 첫 번째 뷰 : challenge id별 total_views, total_unique_views 합계를 구합니다.
• 두 번째 뷰 : challenge id별 total_submissions, total_accepted_submissions 합계를 구합니다.
• 세 번째 뷰 : contests - colleges - challenges 를 조인합니다. 테이블 간의 관계가 1:many 이므로 left join을 사용합니다.

 

최종 output 만들기

select a.contest_id, a.hacker_id, a.name, sum(sum_sub.s3), sum(sum_sub.s4), sum(sum_view.s1), sum(sum_view.s2)
from cte a
left join sum_view on a.challenge_id = sum_view.challenge_id
left join sum_sub on a.challenge_id = sum_sub.challenge_id
group by a.contest_id, a.hacker_id, a.name
having (sum(sum_view.s1) + sum(sum_view.s2) + sum(sum_sub.s3) + sum(sum_sub.s4)) > 0
order by a.contest_id

• from : challenge_id를 사용하여 인라인 뷰 3개를 join합니다. cte를 기준으로 join하기 위해 left join을 사용합니다.
• select - group by : contest_id, hacker_id, name을 기준으로 group by 한 뒤, 합계를 한 번씩 더 구합니다.
  • contest_id만 group by 기준으로 삼을 경우, hacker_id와 name은 집계 연산자가 아니기 때문에 출력할 수 없습니다.
  • 인라인 뷰와 메인 쿼리에서 모두 group by - sum을 사용하는 이유 : 메인 쿼리의 group by 기준인 contest_id와 인라인 뷰의 group by 기준인 challenge_id는 1:many의 관계이기 때문입니다.
• having : 네 집계 컬럼이 모두 0인 경우를 제외합니다.
• order by : contest_id를 기준으로 정렬합니다.