Hackerrank - challenges

1. 문제

 

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.

https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true

 

2. 문제 조건

 

1. Write a query to print the hacker_id, name, and the total number of challenges created by each student. -> GROUP BY를 통해 COUNT() 집계함수 활용
2. 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. -> ORDER BY
3. 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. 
   1. challenge 수가 중복되는데, MAX(challenge)가 아닌 경우 -> 제외
   2. MAX(challenge)인 경우나 challenge 수가 중복되지 않는 경우 -> 포함

 

3. 코드 작성

 

- subquery만 활용

SELECT h.hacker_id
     , h.name
     , COUNT(*) AS challenges_created
FROM challenges AS c
    JOIN hackers AS h ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
    -- MAX(challenge)일 경우
    HAVING challenges_created = (SELECT COUNT(*) AS cc
                                FROM challenges
                                GROUP BY hacker_id
                                ORDER BY cc DESC
                                LIMIT 1)
    -- 중복되는 challenge수가 없는 경우
    OR challenges_created IN (SELECT sub.cc
                                FROM (SELECT hacker_id
                                           , COUNT(*) AS cc
                                    FROM challenges
                                    GROUP BY hacker_id) AS sub
                                GROUP BY sub.cc
                                    HAVING COUNT(*) = 1)
ORDER BY challenges_created DESC, c.hacker_id;

 

-subquery + with문 활용

WITH counter AS (
        SELECT hackers.hacker_id
             , hackers.name
             , COUNT(*) AS challenges_created
        FROM hackers JOIN challenges ON hackers.hacker_id = challenges.hacker_id
        GROUP BY hackers.hacker_id, hackers.name)
        
SELECT *
FROM counter
WHERE challenges_created = (SELECT MAX(counter.challenges_created)
                           FROM counter)
    OR challenges_created IN (SELECT counter.challenges_created
                            FROM counter
                            GROUP BY challenges_created
                            HAVING COUNT(*) = 1)
ORDER BY counter.challenges_created DESC, counter.hacker_id

 

4. 회고

 

• 문제를 풀기 전에 조건을 정리헤놓고, 논리구조를 미리 생각해놓아야 수월하게 풀 수 있다.
• WITH문을 통해서, 반복되는 연산을 미리 사전에 선언해놓고 이후에 SELECT를 통해서 사용한다면 훨씬 깔끔하고 효율적으로 쿼리를 작성할 수 있다.
  • WITH 테이블명 AS ()
• 논리구조가 복잡하다면, 하나하나 따로 실행해보면서 조건을 만들어 나가는 것도 한 방법이다.