[HackerRank] Interviews

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

Interviews | HackerRank

table 사이의 관계를 잘 파악하는게 엄청 중요했던 문제

table이 5개씩이나 돼서 관계 파악이 어려웠다

+ JOIN 할 때 JOIN 기준이 되는 key 값이 해당 table에 여러개인지 아닌지 확인해야 중복이 일어나지 않는다는걸 배움!

 

SELECT con.contest_id,
       con.hacker_id,
       con.name,
       SUM(s.total_submissions),
       SUM(s.total_accepted_submissions),
       SUM(v.total_views),
       SUM(v.total_unique_views)
FROM contests con
LEFT JOIN colleges col ON con.contest_id = col.contest_id
LEFT JOIN challenges chal ON col.college_id = chal.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
) v ON chal.challenge_id = v.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
) s ON chal.challenge_id = s.challenge_id
GROUP BY con.contest_id,
         con.hacker_id,
         con.name
HAVING SUM(s.total_submissions) + SUM(s.total_accepted_submissions) + SUM(v.total_views) + SUM(v.total_unique_views) != 0;