[HackerRank] Interviews

1. 문제

 

해석

• contest_id, hacker_id, name 출력
• 각 대회 별 total_submissions, total_accepted_submissions, total_views, total_unique_views의 합계를 출력
• 4개의 합계가 모두 0이면 해당 대회를 결과에서 제외
• 단 특정 대회는 한 개 이상의 대학에서 후보자를 선별하는데 활용될 수 있지만, 각 대학은 하나의 대만 개최

해답예시

 

2. 풀이

1. 대회에 속한 챌린지를 확인하기 위해 Contests, Colleges, Challenges 테이블을 Join 한다.

SELECT A.contest_id, A.hacker_id, A.name 
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) AS TOTAL_SUBMISSIONS,
SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSIONS
FROM SUBMISSION_STATS
GROUP BY CHALLENGE_ID

SELECT CHALLENGE_ID, SUM(TOTAL_VIEWS) AS TOTAL_VIEWS,
SUM(TOTAL_UNIQUE_VIEWS) AS 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
;

 

혼자서 고민해도 풀이가 어려워 아래의 블로그를 정말 많이 참고했다.

https://yurimyurim.tistory.com/13

[HackerRank] Interviews 풀이 (Oracle)

잘못된 내용이 있다면 지적부탁드립니다. 방문해주셔서 감사합니다.