Interviews

SELECT 
    c.contest_id, 
    c.hacker_id, 
    c.name,
    SUM(S.sum_total_submissions),
    SUM(S.sum_total_accepted_submissions),
    SUM(V.sum_total_view),
    SUM(V.sum_total_unique_view)
FROM Contests c 
JOIN Colleges col ON c.contest_id = col.contest_id 
JOIN Challenges ch ON col.college_id = ch.college_id 
LEFT JOIN 
    (
        SELECT challenge_id, SUM(total_views) AS sum_total_view , SUM(total_unique_views) AS sum_total_unique_view
        FROM View_Stats
        GROUP BY challenge_id
    ) AS V ON V.challenge_id = ch.challenge_id
LEFT JOIN
    (
        SELECT challenge_id, SUM(total_submissions) AS sum_total_submissions , SUM(total_accepted_submissions) AS sum_total_accepted_submissions
        FROM Submission_Stats
        GROUP BY challenge_id
    ) AS S ON S.challenge_id = ch.challenge_id
GROUP BY c.contest_id, c.hacker_id, c.name
HAVING     SUM(V.sum_total_view) +
    SUM(V.sum_total_unique_view) +
    SUM(S.sum_total_submissions) +
    SUM(S.sum_total_accepted_submissions) > 0
ORDER BY c.contest_id

<2025년 5월 7일 복습>

 

SELECT con.contest_id,
       con.hacker_id,
       con.name,
       sum(total_submissions2),
       sum(total_accepted_submissions2),
       sum(total_views2),
       sum(total_unique_views2)
FROM Contests con
JOIN Colleges col ON con.contest_id = col.contest_id
JOIN Challenges ch ON col.college_id = ch.college_id
LEFT JOIN (
        SELECT challenge_id,
              SUM(total_views) AS total_views2,
              SUM(total_unique_views) AS total_unique_views2
        FROM View_Stats
        GROUP BY challenge_id
) AS V ON V.challenge_id = ch.challenge_id
LEFT JOIN (
        SELECT challenge_id,
                SUM(total_submissions) AS total_submissions2,
                SUM(total_accepted_submissions) AS total_accepted_submissions2
        FROM Submission_Stats
        GROUP BY challenge_id
) AS ss ON ch.challenge_id = ss.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING sum(total_submissions2) +
       sum(total_accepted_submissions2) +
       sum(total_views2) +
        sum(total_unique_views2) > 0
ORDER BY con.contest_id

코드는 거의 유사하지만 다시풀어도 정말 어렵다.

FK나 PK가 아닌 컬럼으로 join 하는건 매우 어렵다고 한다. 겹치는게 있기 마련이므로 DISTINCT나 GROUP BY로 통일을 시켜줘야 한다고 한다. 여기서는 챌린지는 겹치는 아이디가 많기 때문에 GROUP BY로 통일을 해줬다. 주말에 이 문제에 대해 심층적으로 리뷰해보자.
https://jinooh.tistory.com/58

HackerRank SQL - Interviews

참고 블로그