[HackerRank][SQL][Advanced Join] Interviews ☆☆

 

https://www.hackerrank.com/challenges/interviews/problem

Interviews | HackerRank

SELECT  CON.contest_id, 
        CON.hacker_id, 
        CON.name, 
        SUM(total_submissions) as A, 
        SUM(total_accepted_submissions) as B, 
        SUM(total_views) as C, 
        SUM(total_unique_views) as D

FROM Contests CON LEFT JOIN Colleges COL ON CON.contest_id = COL.contest_id
     LEFT JOIN Challenges CHA ON COL.college_id = CHA.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
       ) AS VIE ON CHA.challenge_id = VIE.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       
       ) AS SUB ON CHA.challenge_id = SUB.challenge_id

GROUP BY contest_id, hacker_id, name
HAVING A+B+C+D>0

ORDER BY CON.contest_id

▶ submission_stats, view_stats 테이블을 VIE, SUB 로 각각의 테이블로 SUBQUERY를 만들어준다

▶ JOIN 없이 활용할 경우는 누락값이