Prepare > SQL > Advanced Join > Interviews

Interviews | HackerRank

 

문제

---

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 .

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.

 

=> 여러 콘테스트와 여러 문제들이 있다. 각 콘테스트별  contest_id, hacker_id, name와 total_submissions, total_accepted_submissions, total_views, and total_unique_views들 의 합계를 쿼리하라. contest_id로 정렬도 하고, 합계들이 모두 0이면 제외하라.

하나의 대학에서 하나의 콘테스트만 개최함. 특정 콘테스트로 후보자를 특정할 수 있음.

 

 

 

 

코드

---

SELECT 
CN.contest_id,
CN.hacker_id,
CN.name,
SUM(SB.total_submissions),
SUM(SB.total_accepted_submissions),
SUM(VS.total_views),
SUM(VS.total_unique_views)

FROM CONTESTS AS CN
    LEFT JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        LEFT JOIN CHALLENGES AS CH ON CL.college_id = CH.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) AS VS
                ON CH.challenge_id = VS.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) AS SB
                    ON CH.challenge_id = SB.challenge_id

                

GROUP BY CN.contest_id, CN.hacker_id, CN.name
HAVING SUM(SB.total_submissions) <> 0 
    AND SUM(SB.total_accepted_submissions) <> 0
    AND SUM(VS.total_views) <> 0
    AND SUM(VS.total_unique_views) <> 0


ORDER BY CN.hacker_id;

 

 

 

 

노트

---

SELECT 
*
FROM CONTESTS AS CN
    INNER JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        INNER JOIN CHALLENGES AS CH ON CL.college_id = CH.college_id
            INNER JOIN VIEW_STATS AS VS ON CH.challenge_id = VS.challenge_id
                INNER JOIN SUBMISSION_STATS AS SB ON CH.challenge_id = SB.challenge_id;

 

SELECT 
CN.contest_id,
CN.hacker_id,
CN.name,
SUM(SB.total_submissions),
SUM(SB.total_accepted_submissions),
SUM(VS.total_views),
SUM(VS.total_unique_views)

FROM CONTESTS AS CN
    INNER JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        INNER JOIN CHALLENGES AS CH ON CL.college_id = CH.college_id
            INNER JOIN VIEW_STATS AS VS ON CH.challenge_id = VS.challenge_id
                INNER JOIN SUBMISSION_STATS AS SB ON CH.challenge_id = SB.challenge_id
                
GROUP BY CN.contest_id, CN.hacker_id, CN.name

845 579 Rose 2566 841 2447 869 
858 1053 Angela 1930 441 1464 511 
883 1055 Frank 2689 734 1794 593 
1793 2655 Patrick 2488 655 2257 762 
2374 2765 Lisa 6349 1924 7405 2026 
2963 2845 Kimberly 8499 2395 7261 2460 
3584 2873 Bonnie 6417 1574 5839 1872 
4044 3067 Michael 2594 984 3000 939 
4249 3116 Todd 2102 546 1729 490 
4269 3256 Joe 3115 1042 3122 950 
4483 3386 Earl 3564 870 3063 791 
4541 3608 Robert 3423 953 3115 821 
4601 3868 Amy 3991 1427 4256 1280 
4710 4255 Pamela 3580 896 3421 1004 
4982 5639 Maria 4849 1304 4414 1213 
5913 5669 Joe 4269 1301 5005 1236 
5994 5713 Linda 6090 1697 5414 1681 
6939 6550 Melissa 6399 1893 6358 1845 
7266 6947 Carol 5873 1435 5370 1627 
7280 7030 Paula 3094 960 2636 858 
7484 7033 Marilyn 7231 2094 7659 2034 
7734 7386 Jennifer 8067 2149 8405 2307 
7831 7787 Harry 7143 1799 5493 1892 
7862 8029 David 2385 719 2611 819 
8812 8147 Julia 1916 494 1785 545 
8825 8438 Kevin 5203 1582 5329 1578 
9136 8727 Paul 7392 2629 7516 2294 
9613 8762 James 7889 2133 8282 2503 
10568 8802 Kelly 4892 1668 5812 1746 
11100 8809 Robin 3382 1091 3326 1138 
12742 9203 Ralph 3443 866 3258 934 
12861 9644 Gloria 2825 903 2840 936 
12865 10108 Victor 2379 709 2182 789 
13503 10803 David 722 230 720 180 
13537 11390 Joyce 2994 1117 3418 1071 
13612 12592 Donna 3358 939 3369 952 
14502 12923 Michelle 3335 1066 2982 1032 
14867 13017 Stephanie 4078 1227 4947 1117 
15164 13256 Gerald 4218 1454 4647 1405 
15804 13421 Walter 3317 1046 3430 1025 
15891 13569 Christina 4337 1447 3770 1386 
16063 14287 Brandon 4565 1438 5161 1674 
16415 14311 Elizabeth 9336 2716 9543 2881 
18477 14440 Joseph 1954 640 2211 747 
18855 16973 Lawrence 7378 2271 7361 2362 
19097 17123 Marilyn 6942 2121 6045 1775 
19575 17562 Lori 4871 1458 4867 1485

 

SELECT 
CN.contest_id,
CN.hacker_id,
CN.name,
SUM(SB.total_submissions),
SUM(SB.total_accepted_submissions),
SUM(VS.total_views),
SUM(VS.total_unique_views)

FROM CONTESTS AS CN
    INNER JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        INNER JOIN CHALLENGES AS CH ON CL.college_id = CH.college_id
            INNER JOIN VIEW_STATS AS VS ON CH.challenge_id = VS.challenge_id
                INNER JOIN SUBMISSION_STATS AS SB ON CH.challenge_id = SB.challenge_id
                

GROUP BY CN.contest_id, CN.hacker_id, CN.name
HAVING SUM(SB.total_submissions) <> 0 
    OR SUM(SB.total_accepted_submissions) <> 0
    OR SUM(VS.total_views) <> 0
    OR SUM(VS.total_unique_views) <> 0


ORDER BY CN.hacker_id;

• 오답!

 

왜 틀렸어...?

SELECT COUNT(DISTINCT(CHALLENGE_ID)), COUNT(CHALLENGE_ID) FROM VIEW_STATS;

862 2000

• view_stats 때문에 join이 1: n 관계가 되면서 row가 복사된 것이 문제.
• 주어진 테이블을 잘 살펴보기

 

1:1 관계로 만들기(total_views와 total_unique_views의 개별 총합 필요)

SELECT 
challenge_id,
SUM(total_views),
SUM(totalunique_views)

FROM VIEW_STATS
GROUP BY challenge_id
;

 

JOIN 하는 테이블 서브쿼리로 바꿔주기

SELECT 
CN.contest_id,
CN.hacker_id,
CN.name,
SUM(SB.total_submissions),
SUM(SB.total_accepted_submissions),
SUM(VS.total_views),
SUM(VS.total_unique_views)

FROM CONTESTS AS CN
    INNER JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        INNER JOIN CHALLENGES AS CH ON CL.college_id = CH.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) AS VS
                ON CH.challene_id = VS.challenge_id
                LEFT JOIN
                (SELECT challenge_id,SUM(total_submissions) as total_submissions ,SUM(total_accepted_submissions) AS total_accepted_submisstions
                FROM SUMISSIONS GROUP BY challenge_id) AS SB
                ON CH.challene_id = VS.challenge_id

                

GROUP BY CN.contest_id, CN.hacker_id, CN.name
HAVING SUM(SB.total_submissions) <> 0 
    OR SUM(SB.total_accepted_submissions) <> 0
    OR SUM(VS.total_views) <> 0
    OR SUM(VS.total_unique_views) <> 0


ORDER BY CN.hacker_id;

 

SELECT 
CN.contest_id,
CN.hacker_id,
CN.name,
SUM(SB.total_submissions),
SUM(SB.total_accepted_submissions),
SUM(VS.total_views),
SUM(VS.total_unique_views)

FROM CONTESTS AS CN
    LEFT JOIN COLLEGES AS CL ON CN.contest_id = CL.contest_id
        LEFT JOIN CHALLENGES AS CH ON CL.college_id = CH.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) AS VS
                ON CH.challenge_id = VS.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) AS SB
                    ON CH.challenge_id = SB.challenge_id

                

GROUP BY CN.contest_id, CN.hacker_id, CN.name
HAVING SUM(SB.total_submissions) +
		SUM(SB.total_accepted_submissions) +
		SUM(VS.total_views) +
		SUM(VS.total_unique_views) <> 0


ORDER BY CN.hacker_id;

• 합이 0이 아닌것들?~?