[SQL] 해커랭크 HackerRank Interviews 문제 풀이

문제

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds  screening contest.

 

해석

이 문제에서는 여러 개의 테이블에서 정보를 결합하여 각 대회의 총 제출 횟수, 승인된 제출 횟수, 조회수, 고유 조회수를 집계하는 SQL 쿼리를 작성해야 해요.

출력해야 할 항목:

1. contest_id (대회 ID)
2. hacker_id (해커 ID)
3. name (해커 이름)
4. total_submissions (총 제출 횟수)
5. total_accepted_submissions (총 승인된 제출 횟수)
6. total_views (총 조회수)
7. total_unique_views (총 고유 조회수)

출력 조건

• 총 제출 횟수, 승인된 제출 횟수, 조회수, 고유 조회수 중 하나라도 0보다 크면 출력해야 함.
• contest_id 기준으로 오름차순 정렬해야 함.

 

 

정답

SELECT
    c.contest_id,
    c.hacker_id,
    c.name,
    COALESCE(SUM(ss.total_submissions), 0) AS total_submissions,
    COALESCE(SUM(ss.total_accepted_submissions), 0) AS total_accepted_submissions,
    COALESCE(SUM(vs.total_views), 0) AS total_views,
    COALESCE(SUM(vs.total_unique_views), 0) AS total_unique_views
FROM
    Contests c
    JOIN Colleges co ON c.contest_id = co.contest_id
    JOIN Challenges ch ON co.college_id = ch.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
            challenge_id
    ) vs ON ch.challenge_id = vs.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
            challenge_id
    ) ss ON ch.challenge_id = ss.challenge_id
GROUP BY
    c.contest_id,
    c.hacker_id,
    c.name
HAVING
    total_submissions > 0
    OR total_accepted_submissions > 0
    OR total_views > 0
    OR total_unique_views > 0
ORDER BY
    c.contest_id;

 

배운 점

• 와 처음으로 정말 복잡한 쿼리였다.... 이건... 음.... 나중에 리뷰를 해봐야 할거 같다.
• 갑자기 난이도가 어려워져서 굉장히 당혹스럽다.