[HackerRank] Interviews 풀이 (Oracle)

문제

Interviews | HackerRank

Interviews | HackerRank

 

문제 해설

Samantha는 코딩 챌린지와 컨테스트를 통해 여러 대학의 많은 후보자를 인터뷰합니다.

contest_id, hacker_id, name과 각 컨테스트 별 total_submissions, total_accepted_submissions, total_views, total_unique_views의 합계를 출력합니다.

4개의 합계가 모두 0이면 해당 컨테스트를 결과에서 제외합니다.

참고: 특정 컨테스트는 한 개 이상의 대학에서 후보자를 선별하는데 활용될 수 있지만, 각 대학은 하나의 컨테스트만 개최합니다.

 

테이블 정보

Contests

Colleges

Challenges

View_Stats

Submission_Stats

 

풀이

[그림] 출력 대상 컬럼(점선 박스)과 Join 구조(화살표)

• 컨테스트별 total_submissions, total_accepted_submissions, total_views, total_unique_views 값의 합계를 구해야 합니다.
• 컨테스트에 속한 챌린지를 확인하기 위해 Contests, Colleges, Challenges 테이블을 Inner Join 합니다.
• View_Stats에만 데이터가 있거나 Submission_Stats에만 데이터가 있을 경우를 위해 두 테이블은 Outer Join 합니다.

 

1. Contests, Colleges, Challenges 테이블을 Inner Join 합니다.

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME
     , C.CHALLENGE_ID
  FROM CONTESTS A
     , COLLEGES B
     , CHALLENGES C
 WHERE A.CONTEST_ID = B.CONTEST_ID
   AND B.COLLEGE_ID = C.COLLEGE_ID
;

 

2. 1의 쿼리와 View_Stats, Submission_Stats 테이블을 바로 Join할 경우 1 : 0 or N 관계 이기 때문에 Challenge_id의 중복된 row만큼 뻥튀기 될 수 있습니다. 먼저 Challenge_id로 그룹핑 해서 1 : 0 or 1 관계가 되도록 준비합니다.

SELECT CHALLENGE_ID
     , SUM(TOTAL_SUBMISSIONS) TOTAL_SUBMISSIONS
     , SUM(TOTAL_ACCEPTED_SUBMISSIONS) TOTAL_ACCEPTED_SUBMISSIONS
  FROM SUBMISSION_STATS
 GROUP BY CHALLENGE_ID
;

SELECT CHALLENGE_ID
     , SUM(TOTAL_VIEWS) TOTAL_VIEWS
     , SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
  FROM VIEW_STATS
 GROUP BY CHALLENGE_ID
;

 

3. 1과 2의 쿼리를 Outer Join하고, 컨테스트 별(contest_id, hacker_id, name)로 그룹핑합니다. 합계가 모두 0일 경우를 제외하는 HAVING 조건도 추가합니다. 최종 쿼리 입니다.

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME
     , SUM(S.TOTAL_SUBMISSIONS)
     , SUM(S.TOTAL_ACCEPTED_SUBMISSIONS)
     , SUM(V.TOTAL_VIEWS)
     , SUM(V.TOTAL_UNIQUE_VIEWS)
  FROM CONTESTS A
     , COLLEGES B
     , CHALLENGES C
     , (SELECT CHALLENGE_ID
             , SUM(TOTAL_SUBMISSIONS) TOTAL_SUBMISSIONS
             , SUM(TOTAL_ACCEPTED_SUBMISSIONS) TOTAL_ACCEPTED_SUBMISSIONS
          FROM SUBMISSION_STATS
         GROUP BY CHALLENGE_ID
       ) S
     , (SELECT CHALLENGE_ID
             , SUM(TOTAL_VIEWS) TOTAL_VIEWS
             , SUM(TOTAL_UNIQUE_VIEWS) TOTAL_UNIQUE_VIEWS
          FROM VIEW_STATS
         GROUP BY CHALLENGE_ID
       ) V
 WHERE A.CONTEST_ID = B.CONTEST_ID
   AND B.COLLEGE_ID = C.COLLEGE_ID
   AND C.CHALLENGE_ID = S.CHALLENGE_ID(+)
   AND C.CHALLENGE_ID = V.CHALLENGE_ID(+)
 GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME
 HAVING
       SUM(S.TOTAL_SUBMISSIONS) > 0 OR SUM(S.TOTAL_ACCEPTED_SUBMISSIONS) > 0
    OR SUM(V.TOTAL_VIEWS) > 0 OR SUM(V.TOTAL_UNIQUE_VIEWS) > 0
 ORDER BY CONTEST_ID
;

 

결과