[MySQL/Hackerrank] Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

Input Format

The following tables contain challenge data:

 

Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 

Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.

영어 질문이 너무 어렵다;;

우선 Hackers, Challenges 테이블을 join 해서 각 hacker_id별 challenge 수를 카운트하는 것은 전혀 문제가 없다.

문제는 challenge의 max값 보다 작으면서 challenge수가 동일한 hacker_id를 제외하는 것이다.

Discussions에서는 challenge의 카운트가 max인 것과 1인 것만 가져오도록 작성한 sql이 많았다.

하지만 나는 처음 생각한 것이 카운트가 max보다 작으면서 1이상인 것을 제외하는 방법이었다.

(미묘하지만 큰 차이가 있다...)

내가 생각한 방법이 비효율적일수도 있겠으나 나는 모든 문제에서 대부분 그런식으로 생각하기 때문에

내가 생각한 대로 풀어내는 것이 목표였다.

 

첫 번째로 과도한 서브쿼리를 방지하기 위해 challenge 수의 max값은 미리 구하여 @max_challenge_id 라는 변수에 넣어놓았다. 

두 번째로 Hackers, Challenges 테이블을 join하여 count(challenge_id)를 구하는 구문을 작성하였다.

마지막으로 where 절에서 제외할 hacker_id 구문을 작성하였다. 

일단 각 hacker_id별 challenge를 카운트한 테이블 2개를 challenge_id 기준으로 join하였다. 이 때 동일한 hacker_id는 나오지 않도록 제외하는 구문을 넣어줘야 한다. hacker_id가 다른데도 결과가 나온다는 것은 질문에서 원하는 다른 hacker임에도 challenge 카운트가 동일함을 의미한다. 나온 결과에서 challenge의 max값은 제외해야 하므로 첫 번쨰에서 구한 @max_challenge_id보다 작아야 한다는 조건을 추가해준다. 

/*
SELECT - hacker_id, name, total number of challenge
FROM Hackers, Challenges
ORDER BY - total number of challenges DESC, hacker_id
WHERE challenge 수가 MAX보다 작으면서 2명 이상의 학생이 동일한 challenge 수이면 제외
*/

SELECT MAX(cnt_challenge_id)
FROM (
    SELECT hacker_id, COUNT(challenge_id) cnt_challenge_id
    FROM Challenges
    GROUP BY hacker_id
) CC
INTO @max_challenge_id;


SELECT A.hacker_id, A.name, COUNT(B.challenge_id) 
FROM Hackers A
    JOIN Challenges B
    ON A.hacker_id = B.hacker_id
WHERE A.hacker_id NOT IN (
       SELECT C1.hacker_id
       FROM (
            SELECT hacker_id, COUNT(challenge_id) cnt_challenge_id
            FROM Challenges
            GROUP BY hacker_id
            ) C1
            JOIN (
            SELECT hacker_id, COUNT(challenge_id) cnt_challenge_id
            FROM Challenges
            GROUP BY hacker_id
            ) C2
            ON C1.cnt_challenge_id = C2.cnt_challenge_id    --challenge_id로 join해서 데이터가 나온다는거 자체가 이미 동일한 challenge가 있다는 것을 의미 
        WHERE C1.hacker_id != C2.hacker_id
            AND C1.cnt_challenge_id < @max_challenge_id
        GROUP BY C1.hacker_id
    )
GROUP BY A.hacker_id, A.name
ORDER BY COUNT(B.challenge_id) DESC, A.hacker_id;