HackerRank SQL - 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. 

Problem

hacker_idx, name, 각 학생의 challenges 합 컬럼을 추출하라.

단, chaleges의 합을 기준으로 내림차순으로 정렬하라.

만약, 같은 수의 challenges 가진 학생이 1명이상이라면,

challenges의 합이 최대값보다 작다면, 해당 학생은 결과에서 제외하고, 그렇지 않다면, hacker_id로 정렬하라.

---

Answer1

SELECT H.hacker_id, H.name, COUNT(C.challenge_id) AS total_of_challenges
  FROM Challenges C LEFT JOIN hackers H ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name 
HAVING total_of_challenges IN (SELECT DISTINCT count(C.challenge_id) temp_ToC
                                 FROM challenges C
                             GROUP BY C.hacker_id
                               HAVING temp_ToC IN (SELECT MAX(T.CNT)
                                                    FROM (SELECT count(*) CNT
                                                            FROM challenges C
                                                        GROUP BY C.hacker_id) T
                                                        )
                                      OR temp_ToC IN (SELECT T.CNT
                                                         FROM (SELECT C.hacker_id, count(*) CNT
                                                                 FROM challenges C
                                                             GROUP BY C.hacker_id) T
                                                    GROUP BY T.CNT HAVING COUNT(*) =1 
                                                     )
                                )
ORDER BY total_of_challenges desc, hacker_id

 

---

How to solve

조건이 난해해서 꽤 복잡한 서브쿼리를 결합해야 하는데,

서브쿼리를 덕지덕지 붙이는 게 성능에는 매우 안좋다는 말을 들은 적이 있어 고민이 되었다.

 

일단 추출해야 하는 메인 SELECT문은 비교적 명확하기에 이거부터 작성해본다.

SELECT H.hacker_id, H.name, COUNT(C.challenge_id) AS total_of_challenges
  FROM Challenges C LEFT JOIN hackers H ON H.hacker_id = C.hacker_id
GROUP BY H.hacker_id, H.name
ORDER BY total_of_challenges desc, hacker_id

 

조건 2가지를 이제 쿼리를 작성해야 되는데 다시 정리해보면 다음과 같다.

같은 수의 challenges 수를 가진 학생이 2명 이상라면, 해당 challenges의 합이 최대값인지 아닌지를 판별하고 최대값이라면 그대로 표시하고(+hacker_id로 정렬), 아니라면 해당 학생 전체를 제외시켜야한다.

 

아래와 같이 challeges의 합이 최대값인 수와 중복이 아닌 수를 구해주는 쿼리를 만들어본다.

#challenges 개수가 최대값인 수 구하기
SELECT MAX(T.CNT)
 FROM (SELECT count(*) CNT
        FROM challenges C
    GROUP BY C.hacker_id) T
      )

 

 

# challenges의 수가 학생별로 중복이 아닌 경우(고유값) 구하기
SELECT T.CNT
 FROM (SELECT C.hacker_id, count(*) CNT
         FROM challenges C
     GROUP BY C.hacker_id) T
GROUP BY T.CNT HAVING COUNT(*) =1

 

마지막으로 해당 쿼리를 having절로 만들어주는데 해당 수는 최대값은 중복으로 들어갈 수 있으니 distinct를 넣어준다.

HAVING total_of_challenges IN (SELECT DISTINCT count(C.challenge_id) temp_ToC
                                 FROM challenges C
                             GROUP BY C.hacker_id
                               HAVING temp_ToC IN (SELECT MAX(T.CNT)
                                                    FROM (SELECT count(*) CNT
                                                            FROM challenges C
                                                        GROUP BY C.hacker_id) T
                                                        )
                                      OR temp_ToC IN (SELECT T.CNT
                                                         FROM (SELECT C.hacker_id, count(*) CNT
                                                                 FROM challenges C
                                                             GROUP BY C.hacker_id) T
                                                    GROUP BY T.CNT HAVING COUNT(*) =1 
                                                     )
                                )

 

역시나 이번에도 성능에는 큰 의문이 남지만, 언젠가 다시 봤을때 콧웃음치고 최적화를 할 수 있는 날이 올까봐 기록해둔다.