코테준비 | 해커랭크 interviews (다중 테이블 join)

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

---

[문제 풀이]

정답 코드

SELECT c.contest_id,c.hacker_id,c.name
      ,SUM(s.ts)
      ,SUM(s.tas)
      ,SUM(v.tv)
      ,SUM(v.tuv)
FROM CONTESTS C
    INNER JOIN COLLEGES CO ON C.CONTEST_ID =CO.CONTEST_ID 
    INNER JOIN Challenges ch on co.college_id = ch.college_id
    LEFT JOIN (SELECT challenge_id,SUM(total_views) AS tv,SUM(total_unique_views) AS tuv
                FROM View_Stats
                GROUP BY challenge_id) AS v on ch.challenge_id = v.challenge_id
    LEFT JOIN (SELECT challenge_id,SUM(total_Submissions)AS ts,SUM(total_accepted_Submissions) AS tas
                FROM Submission_Stats
                GROUP BY challenge_id) AS s on ch.challenge_id = s.challenge_id
GROUP BY c.contest_id,c.hacker_id,c.name
HAVING SUM(s.ts)<>0 AND SUM(s.tas)<>0 AND SUM(v.tv)<>0 AND SUM(v.tuv) <> 0
ORDER BY 1

 

이슈 및 해결 과정

• 1st  시도 ( 오류 코드) 및 아이디어 
  
  • 5개 테이블 전부 inner join
  • contest_id 기준으로 total 컬럼들 합 집계
  • 모든 total 값들의 합이 0이 아닌 것만 필터링
  • contest_id로 정렬 

-- 1st error
SELECT c.contest_id,c.hacker_id,c.name
      ,SUM(s.total_submissions) AS ts
      ,SUM(s.total_accepted_submissions) AS tas
      ,SUM(v.total_views) as tv
      ,SUM(v.total_unique_views) AS tuv 
FROM CONTESTS C
    INNER JOIN COLLEGES CO ON C.CONTEST_ID =CO.CONTEST_ID 
    INNER JOIN Challenges ch on co.college_id = ch.college_id
    INNER JOIN  View_stats v on ch.challenge_id = v.challenge_id
    INNER JOIN submission_stats s on ch.challenge_id = s.challenge_id
GROUP BY c.contest_id,c.hacker_id,c.name
HAVING ts <>0 AND tas <>0 AND tv <>0 AND tuv <>0
ORDER BY 1

• 문제상황1 : 오답 처리
  • view_stats & submission_stats 테이블은 각각 집계 테이블임 -> 집계 테이블에서 challenge_id 가 다른 집계값들이 다수 존재 
  •  해결과정1 : view_stats & submission_stats 집계 테이블을 challenge_id 기준으로 집계 한 뒤 join 연결 -> challenge_id 중복값을 처리하여 정확하게 집계 

-- 2nd error
SELECT c.contest_id,c.hacker_id,c.name
      ,SUM(s.ts)
      ,SUM(s.tas)
      ,SUM(v.tv)
      ,SUM(v.tuv)
FROM CONTESTS C
    INNER JOIN COLLEGES CO ON C.CONTEST_ID =CO.CONTEST_ID 
    INNER JOIN Challenges ch on co.college_id = ch.college_id
    INNER JOIN (SELECT challenge_id,SUM(total_views) AS tv,SUM(total_unique_views) AS tuv
                FROM View_Stats
                GROUP BY challenge_id) AS v on ch.challenge_id = v.challenge_id
    INNER JOIN (SELECT challenge_id,SUM(total_Submissions)AS ts,SUM(total_accepted_Submissions) AS tas
                FROM Submission_Stats
                GROUP BY challenge_id) AS s on ch.challenge_id = s.challenge_id
GROUP BY c.contest_id,c.hacker_id,c.name
HAVING SUM(s.ts)<>0 AND SUM(s.tas)<>0 AND SUM(v.tv)<>0 AND SUM(v.tuv) <> 0
ORDER BY 1

• 문제상황2 : 오답 처리
  • view_stats & submission_stats 집계 테이블을  Challenge 테이블과 INNER JOIN 진행시, view_stats 테이블에는 있고, submission_stats 테이블에는 없는 challenge_id 는 자동 제외됨. 
  • 하지만 모든 challenge_id 에 해당하는 total 값은 포함되어야함. 
  •  해결과정2 : challenges 테이블과 재 가공한  v & s 테이블을 Left join 으로 연결하여 challenge_id 를 올바르게 join 함. 
•  최종 정답
  • 모든 total 집계값의 합이 0이 아니여야 함
  • `HAVING SUM(s.ts)<>0 AND SUM(s.tas)<>0 AND SUM(v.tv)<>0 AND SUM(v.tuv) <> 0` 필터링했으나
  • `HAVING SUM(s.ts) +SUM(s.tas) +SUM(v.tv)+SUM(v.tuv) > 0 `조건으로 필터링 가능 

-- right code
SELECT c.contest_id,c.hacker_id,c.name
      ,SUM(s.ts)
      ,SUM(s.tas)
      ,SUM(v.tv)
      ,SUM(v.tuv)
FROM CONTESTS C
    INNER JOIN COLLEGES CO ON C.CONTEST_ID =CO.CONTEST_ID 
    INNER JOIN Challenges ch on co.college_id = ch.college_id
    LEFT JOIN (SELECT challenge_id,SUM(total_views) AS tv,SUM(total_unique_views) AS tuv
                FROM View_Stats
                GROUP BY challenge_id) AS v on ch.challenge_id = v.challenge_id
    LEFT JOIN (SELECT challenge_id,SUM(total_Submissions)AS ts,SUM(total_accepted_Submissions) AS tas
                FROM Submission_Stats
                GROUP BY challenge_id) AS s on ch.challenge_id = s.challenge_id
GROUP BY c.contest_id,c.hacker_id,c.name
HAVING HAVING SUM(s.ts) +SUM(s.tas) +SUM(v.tv)+SUM(v.tuv) > 0 
ORDER BY 1

 

한줄 포인트

• 다중 join 진행시, join key & join 방식을 잘 고려해야함. 출력값 오류시, join 이 올바르게 됐는지 선 확인 해볼 필요가 있음