[HackerRank] Interviews (Advanced Join) 풀이

Advanced Join / Hard

 

문제

✨ 문제 링크 : https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true

• contest_id, hacker_id, name 별로 다음 컬럼의 합계를 구해야한다.
• sums of total_submissions & total_accepted_submissions & total_views & total_unique_views for each contest 

👉 주어진 컬럼 자체를 JOIN 하지 않고, 서브쿼리로 합계 컬럼을 각각 가공한 후 해당 테이블을 JOIN 하는 방법을 사용하여 해결한다.

 

---

풀이

1. contest_id, hacker_id, name 컬럼이 들어있는 Contest 테이블을 기준으로, Colleges 테이블과 Challenges을 조인한다

SELECT ct.contest_id, ct.hacker_id, ct.name
FROM Contests AS ct
INNER JOIN Colleges AS c ON ct.contest_id = c.contest_id
LEFT JOIN Challenges AS ch ON ch.college_id = c.college_id

 

 

2. 합계 컬럼 계산 및 조인

• View_Stats 테이블에서 challenge_id 별로 groupby를 사용하여 total_views와 total_unique_views의 합계(SUM)를 계산한다
• 동일하게 Submission_Stats 테이블에서 challenge_id 별로 groupby를 사용하여 total_submissions와 total_accepted_submissions의 합계(SUM)를 계산한다.
• 두 테이블을 서브쿼리로 작성하여 LEFT JOIN 으로 1번 테이블에 연결하되, 조인키는 challenge_id로 지정한다.

SELECT ct.contest_id, ct.hacker_id, ct.name....(작성중)
FROM Contests AS ct
INNER JOIN Colleges AS c ON ct.contest_id = c.contest_id
LEFT JOIN Challenges AS ch ON ch.college_id = c.college_id
LEFT JOIN (SELECT challenge_id,
                  SUM(total_views) as total_views, 
                  SUM(total_unique_views) as total_unique_views
           FROM View_Stats
           GROUP BY 1) AS vs ON vs.challenge_id = ch.challenge_id
LEFT JOIN (SELECT challenge_id,
                  SUM(total_submissions) as total_submissions,
                  SUM(total_accepted_submissions) as total_accepted_submissions
           FROM Submission_Stats
           GROUP BY 1) AS ss ON ss.challenge_id = ch.challenge_id

 

3. 조건 필터링 및 마무리

• contest_id, hacker_id, name을 기준으로 Groupby하여 2에서 구한 합계 값의 합계를 구한다.
• 4개 합계 값이 모두 0인 경우를 제외하기 위한 조건을 Having 절에 지정한다.
• contest_id를 기준으로 Order by 하여 마무리한다.

SELECT ct.contest_id, ct.hacker_id, ct.name, 
       SUM(total_submissions),
       SUM(total_accepted_submissions),
       SUM(total_views),
       SUM(total_unique_views)
FROM Contests AS ct
INNER JOIN Colleges AS c ON ct.contest_id = c.contest_id
LEFT JOIN Challenges AS ch ON ch.college_id = c.college_id
LEFT JOIN (SELECT challenge_id,
                  SUM(total_views) as total_views, 
                  SUM(total_unique_views) as total_unique_views
           FROM View_Stats
           GROUP BY 1) AS vs ON vs.challenge_id = ch.challenge_id
LEFT JOIN (SELECT challenge_id,
                  SUM(total_submissions) as total_submissions,
                  SUM(total_accepted_submissions) as total_accepted_submissions
           FROM Submission_Stats
           GROUP BY 1) AS ss ON ss.challenge_id = ch.challenge_id
GROUP BY 1, 2, 3
HAVING SUM(total_submissions) != 0 OR
       SUM(total_accepted_submissions) != 0 OR
       SUM(total_views) != 0 OR
       SUM(total_unique_views)
ORDER BY 1