해당 문제는 HackerRank에서 사용한 문제이며 모든 테이블의 자료와 출처는 Hackerank임을 밝힙니다.
해당 글에서는 많은 JOIN과 LEFT JOIN을 사용하여 푸는 문제로 난이도가 어느정도 있는 문제입니다.
해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.
시작하겠습니다
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 0.
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.
사만다는 코딩 챌린지와 콘테스트를 통해 다양한 대학의 많은 지원자들을 인터뷰합니다. 쿼리를 작성하여 contest_id, hacker_id, name, 그리고 각 콘테스트를 contest_id별로 정렬한 총_submissions, total_accepted_submissions, total_views, total_unique_views의 합계를 출력합니다. 네 개의 합계가 모두 0인 경우, 결과에서 콘테스트를 제외합니다.
참고: 특정 대회는 여러 대학에서 후보자를 선발하는 데 사용할 수 있지만, 각 대학은 단 하나의 선발 대회만 개최합니다.
해당 문제는 테이블이 많습니다. 그래서 JOIN을 통해 묶어주었으며 계산이 필요한 테이블은 먼저 GROUP BY를 사용하여 계산을 해준 뒤 LEFT JOIN을 통해서 유저 중 챌린지를 하지 않은 것은 점수 부여를 0으로 해주었습니다.
이렇게만 해주면 간단히 풀 수 있을 것입니다.
1. 점수 관련 테이블 더해주기
select challenge_id,
sum(total_views) total_views,
sum(total_unique_views) total_unique_views
from View_Stats
group by challenge_id select challenge_id,
sum(total_submissions) total_submissions,
sum(total_accepted_submissions) total_accepted_submissions
from Submission_Stats
group by challenge_id이렇게 두 개의 테이블을 challenge_id로 그룹화 해준 뒤 각 점수들을 모두 더해주었습니다.
이제 해당 데이터들을 모두 LEFT JOIN을 해줄 것입니다.
2. JOIN과 LEFT JOIN을 활용하기
select *
from contests a
join colleges b on a.contest_id = b.contest_id
join challenges c on b.college_id = c.college_id
left join (
select challenge_id,
sum(total_views) total_views,
sum(total_unique_views) total_unique_views
from View_Stats
group by challenge_id
) d on c.challenge_id = d.challenge_id
left join (
select challenge_id,
sum(total_submissions) total_submissions,
sum(total_accepted_submissions) total_accepted_submissions
from Submission_Stats
group by challenge_id
) e on c.challenge_id = e.challenge_id이렇게 유저와 대학교 관련된 테이블은 INNER JOIN을 적용해줍니다.
그리고 점수 테이블과 유저 테이블을 INNER JOIN을 하지 않은 이유는 만약 유저와 challenge_id 서로 겹치지 않는 것이 존재 한다면 해당 유저는 사라지기 때문에 LEFT JOIN을 활용한 것입니다.
3. 최종적으로 유저가 풀은 문제들 점수 모두 합치고 문제 조건 넣기
select a.contest_id,
a.hacker_id,
a.name,
sum(total_submissions),
sum(total_accepted_submissions),
sum(total_views),
sum(total_unique_views)
from contests a
join colleges b on a.contest_id = b.contest_id
join challenges c on b.college_id = c.college_id
left join (
select challenge_id,
sum(total_views) total_views,
sum(total_unique_views) total_unique_views
from View_Stats
group by challenge_id
) d on c.challenge_id = d.challenge_id
left join (
select challenge_id,
sum(total_submissions) total_submissions,
sum(total_accepted_submissions) total_accepted_submissions
from Submission_Stats
group by challenge_id
) e on c.challenge_id = e.challenge_id
group by a.contest_id, a.hacker_id, a.name
having sum(total_submissions) != 0 OR
sum(total_accepted_submissions) != 0 OR
sum(total_views) != 0 OR
sum(total_unique_views) != 0
order by a.contest_id이렇게 GROUP BY를 사용하여 묶어 준 뒤 SUM()을 사용하여 각 점수들을 모두 더해줍니다.
이제 문제 조건 중 네 개의 점수 모두 0인 값은 제외 시키기 위해 HAVING절에 OR를 사용하여 제외 시켜주었습니다.
ORDER BY를 통해 정렬을 해주면 정답이 됩니다.
이상으로 Hacker Rank 문제 풀이를 마치도록 하겠습니다.
