SQL 코드카타 179 - Interviews

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true

Interviews | HackerRank

 

Column	Type
contest_id	Integer
hacker_id	Integer
name	String

 

Contests 테이블:

• contest_id  : 콘테스트의 id
• hacker_id : 콘테스트를 만든 해커의 id
• name : 해커의 이름

 

Column	Type
college_id	Integer
contest_id	Integer

 

Colleges 테이블:

• college_id : 대학의 id
• contest_id : 후보들을 선발하는데 사용한 콘테스트의 id

 

Column	Type
challenge_id	Integer
college_id	Integer

 

Challenges  테이블:

• challenge_id : 어느 콘테스트에 속하는지 챌린지의 id
• college_id : 후보들이 챌린지를 받은 대학의 id

 

Column	Type
challenge_id	Integer
total_views	Integer
total_unique_views	Integer

 

View_Stats 테이블:

• challenge_id  : 챌린지의 id
• total_views : 후보들이 챌린지를 조회한 총 횟수
• total_unique_views : 고유한 후보자들이 챌린지를 조회한 총 횟수

 

Column	Type
challenge_id	Integer
total_submissions	Integer
total_accepted_submissions	Integer

 

Submission_Stats 테이블:

• challenge_id  : 챌린지의 id
• 챌린지에 대한 총 제출물의 수
• 만점을 받은 제출물의 총 수

각 콘테스트의 contest_id, hacker_id, name, total_submissions, total_accepted_submissions, total_views, total_unique_views의 합계를 출력하는 쿼리를 작성하세요. 이 네 가지 합계가 모두 0인 콘테스트는 결과에서 제외하세요.

참고: 특정 콘테스트는 여러 대학에서 후보자를 선발하는 데 사용될 수 있지만, 각 대학은 단 하나의 선발 콘테스트만 개최합니다.

 

select *
from Contests con
left join Colleges coll on con.contest_id = coll.contest_id
left join Challenges chal on coll.college_id = chal.college_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

 

View_Stats 테이블과 Submission_Stats 테이블을 결합하기 전에 두 테이블을 각각 challenge_id를 기준으로 결합해서 challenge_id 별 합계를 조회한다.

 

select *
from Contests con
left join Colleges coll on con.contest_id = coll.contest_id
left join Challenges chal on coll.college_id = chal.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
) vs on chal.challenge_id = vs.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
) ss on chal.challenge_id = ss.challenge_id

 

두 쿼리를 메인 쿼리와 결합한다.

 

select
    con.contest_id,
    con.hacker_id,
    con.name,
    coalesce(sum(ss.total_submissions), 0),
    coalesce(sum(ss.total_accepted_submissions), 0),
    coalesce(sum(vs.total_views), 0),
    coalesce(sum(vs.total_unique_views), 0)
from Contests con
left join Colleges coll on con.contest_id = coll.contest_id
left join Challenges chal on coll.college_id = chal.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
) vs on chal.challenge_id = vs.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
) ss on chal.challenge_id = ss.challenge_id
group by
    con.contest_id,
    con.hacker_id,
    con.name

 

출력해야할 데이터(contest_id, hacker_id, name)는 각각 고유한 데이터이므로 해당 값들을 기준으로 그룹화 해준 뒤, 그룹별로 total submissions, total accepted submissions, total view, unique views의 합계를 구한다.

그리고 데이터의 값이 없다면 0을 출력하도록 한다.

 

select *
from
(select
    con.contest_id,
    con.hacker_id,
    con.name,
    coalesce(sum(ss.total_submissions), 0) total_submissions,
    coalesce(sum(ss.total_accepted_submissions), 0) total_accepted_submissions,
    coalesce(sum(vs.total_views), 0) total_views,
    coalesce(sum(vs.total_unique_views), 0) total_unique_views
from Contests con
left join Colleges coll on con.contest_id = coll.contest_id
left join Challenges chal on coll.college_id = chal.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
) vs on chal.challenge_id = vs.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
) ss on chal.challenge_id = ss.challenge_id
group by
    con.contest_id,
    con.hacker_id,
    con.name) a
where
    total_submissions > 0 or
    total_accepted_submissions > 0 or
    total_views > 0 or
    total_unique_views > 0

 

위의 쿼리를 서브쿼리로 하고 where 절을 이용해서 모든 데이터가 0이 아닌 데이터만 출력하도록 하면 완료!

 

쿼리가 너무 길어져서 실수도 많았고 많이 헷갈렸던 문제였다.