HackerRank: SQL 풀이 59

59. Advanced Join: Interviews

• 오답

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
WITH CTE AS (
    SELECT Ct.contest_id, 
    Ct.hacker_id, 
    Ct.name, 
    Cl.college_id,
    Cll.challenge_id,
    VS.total_views,
    VS.total_unique_views,
    SS.total_submissions,
    SS.total_accepted_submissions
    FROM Contests Ct
    INNER JOIN Colleges Cl ON Ct.contest_id = Cl.contest_id
    INNER JOIN Challenges Cll ON Cl.college_id = Cll.college_id
    INNER JOIN View_Stats VS ON Cll.challenge_id = VS.challenge_id
    INNER JOIN Submission_Stats SS ON VS.challenge_id = SS.challenge_id
)
SELECT 
    contest_id, 
    hacker_id, 
    name,
    SUM(SS.total_submissions, SS.total_accepted_submissions, VS.total_views, VS.total_unique_views) AS total
FROM CTE
GROUP BY contest_id, hacker_id, name
HAVING SUM(SS.total_submissions, SS.total_accepted_submissions, VS.total_views, VS.total_unique_views) > 0;

틀린 이유: 잘못된 SUM 사용

SELECT 
    SUM(SS.total_submissions) AS TotalSubmissions,
    SUM(SS.total_accepted_submissions) AS TotalAcceptedSubmissions
FROM 
    Submission_Stats SS;

SUM 함수는 그룹화된 데이터에 대해서 합계를 계산하는 데 사용된다.

• 그러나 또 틀린 쿼리

WITH CTE AS (
    SELECT Ct.contest_id, 
    Ct.hacker_id, 
    Ct.name, 
    Cl.college_id,
    Cll.challenge_id,
    VS.total_views,
    VS.total_unique_views,
    SS.total_submissions,
    SS.total_accepted_submissions,
    (SS.total_submissions + SS.total_accepted_submissions + VS.total_views + VS.total_unique_views) AS total
    FROM Contests Ct
    INNER JOIN Colleges Cl ON Ct.contest_id = Cl.contest_id
    INNER JOIN Challenges Cll ON Cl.college_id = Cll.college_id
    INNER JOIN View_Stats VS ON Cll.challenge_id = VS.challenge_id
    INNER JOIN Submission_Stats SS ON VS.challenge_id = SS.challenge_id
)
SELECT 
    contest_id, 
    hacker_id, 
    name,
    SUM(total_submissions),
    SUM(total_accepted_submissions),
    SUM(total_views),
    SUM(total_unique_views)
FROM CTE
GROUP BY contest_id, hacker_id, name
HAVING SUM(total) > 0
ORDER BY contest_id;

• 이게 오답 쿼리인 이유

1. 중복 데이터에 대한 고려 X
   오답 쿼리에서는 각 challenge_id가 여러 테이블 간 INNER JOIN으로 연결할 때 중복 데이터가 발생할 수 있음
   • 예를 들어서 하나의 challenge_id가 여러 대학에 걸쳐 있을 경우에 데이터가 중복되어서 결과적으로 합산 값이 실제 값보다 커질 수 있음
     (실제 잘못된 쿼리와 정답 쿼리 비교시에 아래의 차이를 보이는데 잘못된 쿼리에서 값이 더 커짐을 확인)

잘못된 쿼리 결과
845 579 Rose 2566 841 2447 869
858 1053 Angela 1930 441 1464 511
883 1055 Frank 2689 734 1794 593

정답 쿼리 결과
845 579 Rose 1987 580 1635 566
858 1053 Angela 703 160 1002 384
883 1055 Frank 1121 319 1217 338

2. 데이터 유실
   이후 보여줄 정답쿼리에서는 LEFT JOIN을 이용하여 데이터를 유실하지 않고 결합한다. 그러나, 오답 쿼리에서는 INNER JOIN만 이용하여서 일부 데이터를 제외시키게 된다.
   이로 인하여 중복 데이터와 누락된 데이터가 혼재하여 결과값이 왜곡된다!

• 정답 쿼리

WITH 
-- 제출 통계 집계
Submissions AS (
    SELECT 
        challenge_id,
        SUM(total_submissions) as total_submissions,
        SUM(total_accepted_submissions) as accepted_submissions
    FROM Submission_Stats 
    GROUP BY challenge_id
),
-- 조회 통계 집계
Views AS (
    SELECT 
        challenge_id,
        SUM(total_views) as total_views,
        SUM(total_unique_views) as unique_views
    FROM View_Stats 
    GROUP BY challenge_id
)

SELECT 
    c.contest_id,
    c.hacker_id, 
    c.name,
    SUM(ISNULL(s.total_submissions, 0)) as total_submissions,
    SUM(ISNULL(s.accepted_submissions, 0)) as accepted_submissions,
    SUM(ISNULL(v.total_views, 0)) as total_views,
    SUM(ISNULL(v.unique_views, 0)) as unique_views
FROM Contests c
    INNER JOIN Colleges col ON c.contest_id = col.contest_id
    INNER JOIN Challenges ch ON ch.college_id = col.college_id
    LEFT JOIN Submissions s ON s.challenge_id = ch.challenge_id
    LEFT JOIN Views v ON v.challenge_id = ch.challenge_id
GROUP BY 
    c.contest_id,
    c.hacker_id,
    c.name
HAVING 
    SUM(ISNULL(s.total_submissions, 0)) + 
    SUM(ISNULL(s.accepted_submissions, 0)) +
    SUM(ISNULL(v.total_views, 0)) + 
    SUM(ISNULL(v.unique_views, 0)) > 0
ORDER BY 
    c.contest_id;

이 문제는 나중에 다시 풀어보면 더 좋을 듯