MySQL 해커랭크 | Interviews (해커랭크 Hard 문제풀이)

 

문제

https://www.hackerrank.com/challenges/interviews/problem?isFullScreen=true

Interviews | HackerRank

• 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.
• A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.

 

테이블

Contests	Colleges	Challenges
• hacker_id is the id of the hacker    who created the contest

Submission_Stats	View_Stats

 

풀이과정

# Submission_Stats 테이블에 같은 challenge_id가 여럿인 이유?

왜 같은 challenge_id가 하나로 합쳐지지 않고, 다른 행에 존재하는 걸까?

예를 들어 challnge_id가 47127인 행이 3개나 있음

(View_Stats 테이블의 상황도 마찬가지임. 같은 challenge_id가 여러 행에 있음)

 

→ 이유를 알기 위해 Challenges 테이블, Colleges 테이블, Contests 테이블을 모두 조인해봄

SELECT ct.hacker_id, cl.contest_id, ch.college_id, ch.challenge_id, ss.total_submissions, ss.total_accepted_submissions
FROM Submission_Stats ss
JOIN Challenges ch ON ch.challenge_id = ss.challenge_id
JOIN Colleges cl ON cl.college_id = ch.college_id
JOIN Contests ct ON ct.contest_id = cl.contest_id
ORDER BY 2

hacker_id	contest_id	college_id	challenge_id	total _submissions	total_accepted _submissions
차례대로 hacker_id, contest_id, college_id, challenge_id, ...

 

▶ 이 결과에서 알 수 있는 사실

• 한 hacker마다 하나의 contest를 개최함
• 한 contest에 다양한 colleges가 참여함
• 한 college가 여러 challenge에 참여함

 

# Submission_Stats 테이블에 있는 challenge_id와 View_Stats에 있는 challenge_id의 양상이 다름!

challenge_id가 47127인 경우가

Submission_Stats 테이블에는 3개 행에 등장하고, View_Stats 테이블에는 2개 행에만 등장함

즉, 두 테이블의 조인에 유의해야 함!

→ 두 테이블 각각에서 각 contest 별로 우리가 구하고자 하는
total_submissions, total_accepted_submissions, total_views, and total_unique_views 의 합계를 구해줘야 함

• Submission_Stats 테이블 : SUM( total_submissions ), SUM( total_accepted_submissions )
• View_Stats 테이블 :  SUM( total_views ), SUM( total_unique_views )

# Submission_Stats
SELECT ct.contest_id, ct.hacker_id, ct.name, SUM(ss.total_submissions) ts, SUM(ss.total_accepted_submissions) tas
FROM Submission_Stats ss
JOIN Challenges ch ON ch.challenge_id = ss.challenge_id 
JOIN Colleges cl ON cl.college_id = ch.college_id
JOIN Contests ct ON ct.contest_id = cl.contest_id
GROUP BY 1,2,3

# View_Stats
SELECT ct.contest_id, ct.hacker_id, ct.name,  SUM(vs.total_views) tv, SUM(vs.total_unique_views) tuv
 FROM View_Stats vs
JOIN Challenges ch ON ch.challenge_id = vs.challenge_id 
JOIN Colleges cl ON cl.college_id = ch.college_id
JOIN Contests ct ON ct.contest_id = cl.contest_id
GROUP BY 1,2,3

 

 

# 정답

SELECT a.contest_id, a.hacker_id, a.name, a.ts, a.tas, b.tv, b.tuv
FROM
( SELECT ct.contest_id, ct.hacker_id, ct.name, SUM(ss.total_submissions) ts, SUM(ss.total_accepted_submissions) tas
FROM Submission_Stats ss
JOIN Challenges ch ON ch.challenge_id = ss.challenge_id 
JOIN Colleges cl ON cl.college_id = ch.college_id
JOIN Contests ct ON ct.contest_id = cl.contest_id
GROUP BY 1,2,3 ) a
LEFT JOIN
( SELECT ct.contest_id, ct.hacker_id, ct.name,  SUM(vs.total_views) tv, SUM(vs.total_unique_views) tuv
 FROM View_Stats vs
JOIN Challenges ch ON ch.challenge_id = vs.challenge_id 
JOIN Colleges cl ON cl.college_id = ch.college_id
JOIN Contests ct ON ct.contest_id = cl.contest_id
GROUP BY 1,2,3 ) b ON a.contest_id = b.contest_id AND a.hacker_id = b.hacker_id AND a.name = b.name
WHERE ts + tas + tv + tuv != 0
ORDER BY 1

 

배운점

테이블이 여러 개 있을 때 같은 컬럼이 보인다고 신나서 무작정 조인할 게 아니라

그 관계성을 살펴본 뒤 신중하게 해야한다..!

 

처음에 Submissions_Stats 테이블과 View_Stats 테이블을 일단 냅다 조인한 뒤 나머지 행들을 SUM 해주고나서

왜 오답이 나오는지 모르겠다고 하고 있었으니..

아직도 테이블의 관계성, 구조를 보는 눈이 많이 부족하다.