[해커랭크] Interviews - MySQL

https://www.hackerrank.com/challenges/interviews/problem

Interviews | HackerRank

---

• 문제
  • Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id.
    • 각 contest의 7가지 항목을 출력하라
  • Exclude the contest from the result if all four sums are 0.
    • 합이 0인 contest는 제외
  •  A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.
    • 각 college은 1개의 contest에 참가
• 풀이

5개의 테이블 관계, 파란색 컬럼들이 select 해야되는 컬럼 

 

1.  Contests, Colleges, Challenges 테이블 join

select con.contest_id
    , con.hacker_id
    , con.name
from contests con
join colleges col 
    on con.contest_id = col.contest_id
join challenges cha 
    on col.college_id = cha.college_id;

 

2. 위의 쿼리와 View_Stats, Submission_Stats 일대다 관계

이 상태로 join하면 challenge_id가 중복된 행만큼 뻥튀기 될 수가 있다

따라서, View_Satats와 Submission_Stats를 challenge_id로 그룹핑하여 일대일 관계가 되도록 한다.

select challenge_id
    , sum(total_views) total_views
    , sum(total_unique_views) total_unique_views 
from View_Stats 
group by challenge_id;

 

select challenge_id
    , sum(total_submissions) total_submissions
    , sum(total_accepted_submissions) total_accepted_submissions 
from Submission_Stats 
group by challenge_id;

 

3. 1과 2 쿼리 left join

select con.contest_id
    , con.hacker_id
    , con.name
from contests con
join colleges col 
    on con.contest_id = col.contest_id
join challenges cha 
    on col.college_id = cha.college_id

left join (
    select challenge_id
        , sum(total_views) total_views
        , sum(total_unique_views) total_unique_views 
    from View_Stats 
    group by challenge_id
) v 
    on cha.challenge_id = v.challenge_id

left join (
    select challenge_id
        , sum(total_submissions) total_submissions
        , sum(total_accepted_submissions) total_accepted_submissions 
    from Submission_Stats 
    group by challenge_id
) s 
    on cha.challenge_id = s.challenge_id

 

4. 문제에서 contest별 total_submissions, total_accepted_submissions, total_views, total_unique_views의 합을 구하라고 했으므로, 그룹화해서 구하기 + 4개 합이 0인거 제외

select con.contest_id
    , con.hacker_id
    , con.name
    , sum(total_submissions)
    , sum(total_accepted_submissions)
    , sum(total_views)
    , sum(total_unique_views)
from contests con
join colleges col 
    on con.contest_id = col.contest_id
join challenges cha 
    on col.college_id = cha.college_id

left join (
    select challenge_id
        , sum(total_views) total_views
        , sum(total_unique_views) total_unique_views 
    from View_Stats 
    group by challenge_id
) v 
    on cha.challenge_id = v.challenge_id

left join (
    select challenge_id
        , sum(total_submissions) total_submissions
        , sum(total_accepted_submissions) total_accepted_submissions 
    from Submission_Stats 
    group by challenge_id
) s 
    on cha.challenge_id = s.challenge_id

group by con.contest_id, con.hacker_id, con.name
having sum(total_submissions) !=0 
    or sum(total_accepted_submissions) != 0
    or sum(total_views) != 0 
    or sum(total_unique_views) !=0
order by con.contest_id;