[Oracle] HackerRank 문제 정리 - 5

Interviews

테이블:

Contests:

Colleges:

Challenges:

View_Stats:

Submission_Stats:

Sample Input:

Contents

Colleges

Challenges

View_Stats

Submission_Stats

Sample Output:

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

 

조건:

Samantha는 코딩 챌린지 및 콘테스트를 사용하여 여러 대학의 많은 후보자를 인터뷰합니다. 
콘테스트_id , 해커_id , 이름 및 콘테스트 _id 로 정렬된 각 콘테스트에 대한
total_submissions , total_accepted_submissions , total_views 및 total_unique_views 의 합계 를
인쇄하는 쿼리를 작성 하십시오 .
4개의 합계가 모두 일치하는 경우 결과에서 대회를 제외합니다..

참고: 특정 콘테스트를 사용하여 두 개 이상의 대학에서 후보자를 선별할 수 있지만 각 대학에서는 1개의 Contest 만을 선택해야 합니다.

Subm_T AS (SELECT challenge_id,
               sum(total_submissions) total_submissions,
               sum(total_accepted_submissions) total_accepted_submissions
        FROM   Submission_Stats
        GROUP BY challenge_id ),
 View_T AS (SELECT challenge_id,
               sum(total_views) total_views,
               sum(total_unique_views) total_unique_views
        FROM   View_Stats
        GROUP BY challenge_id ),    
stats AS (SELECT COALESCE(Subm_T.challenge_id, View_T.challenge_id) challenge_id,
               Subm_T.total_submissions total_submissions,
               Subm_T.total_accepted_submissions total_accepted_submissions,
               View_T.total_views total_views,
               View_T.total_unique_views total_unique_views
        FROM   Subm_T FULL OUTER JOIN View_T ON Subm_T.challenge_id = View_T.challenge_id ),
challenge_stats as (SELECT a.college_id,
               sum(b.total_submissions) total_submissions,
               sum(b.total_accepted_submissions) total_accepted_submissions,
               sum(b.total_views) total_views,
               sum(b.total_unique_views) total_unique_views
        FROM   Challenges a JOIN stats b ON a.challenge_id = b.challenge_id
        GROUP BY a.college_id ),
 college_stats as (SELECT a.contest_id as contest_id,
               sum(b.total_submissions) total_submissions,
               sum(b.total_accepted_submissions) total_accepted_submissions,
               sum(b.total_views) total_views,
               sum(b.total_unique_views) total_unique_views
        FROM   Colleges a JOIN challenge_stats b ON a.college_id = b.college_id
        GROUP BY a.contest_id )
SELECT a.contest_id,
       a.hacker_id,
       a.name,
       b.total_submissions,
       b.total_accepted_submissions,
       b.total_views,
       b.total_unique_views
FROM   Contests a JOIN college_stats b on a.contest_id = b.contest_id
ORDER BY contest_id;

쿼리 해석

With 절을 사용하여 여러개의 서브쿼리를 만들어 풀이했습니다. 
맨 처음, Subm_T 라는 서브쿼리에선, Submission_Stats 테이블의 Challenge_id 별 합을 만들어주었고,
View_T 라는 서브쿼리에선, View_Stats 테이블의 Challenge_id 별 합을 만들어주었습니다.
Stats라는 테이블에선 위의 두개 임시테이블을 아웃터 조인을 통해 하나로 만들어주었고
Challenge_Stats라는 테이블은 Challenge_id로 엮여있는 Stats 테이블을 college_id로 그룹화하고 더해준뒤 엮어줍니다.
College_Stats 테이블은 college_id로 되어있는 합들을 contest별로 그룹화한뒤 합쳐줍니다.

마지막으로 College_Stats 테이블과 Contest를 조인하여 최종 답을 출력해줍니다.