Prepare > SQL > Basic Join > Challenges

Challenges | HackerRank

 

문제

---

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.

 

=> 코딩챌린지가 끝났다. hacekr_id, name, 각 학생이 만든 문제 수(CNT) 를  CNT, hacker_id 기준으로 정렬해 쿼리하라.

최다 출제자는 모두 쿼리, 출제 문제수가 같은 학생들이 있다면 그 학생들은 모두 제외.

 

 

 

 

코드

---

WITH K AS (
SELECT H.hacker_id, H.name, COUNT(*) AS CNT
    FROM Challenges AS C
         INNER JOIN HACKERS AS H ON C.hacker_id = H.hacker_id
    GROUP BY H.hacker_id, H.name
)

SELECT 
K.hacker_id,
K.name,
CNT

FROM K

WHERE CNT = (SELECT MAX(CNT) FROM K)
    OR CNT IN (SELECT CNT FROM K GROUP BY CNT HAVING COUNT(*) = 1)
    
ORDER BY CNT DESC, K.hacker_id ASC;

• WITH ~~ AS () 를 쓰면 새로운 테이블이나, 변수 등을 임시로 만들어 두는 효과가 있다.

 

 

 

 

노트

• K.H.hacker_id 같이 쓰지 않아도 된다.
• WITH문 을 적절히 사용하면 쿼리를 짧고 효율적으로 쓸 수 있음
• 서브쿼리를 테이블로 하는 SELECT 문에서 서브쿼리의 컬럼을 조회하려면 서브쿼리의 컬럼의 별칭을 정해줘야 한다.
• 문제를 세부적으로 쪼개어 한 단계씩 써보자.

첫째, 필요한 내용들이 담긴 요약 테이블 작성

SELECT H.hacker_id, H.name, COUNT(*) AS CNT
FROM Challenges AS C
	INNER JOIN hackers AS H ON C.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name

둘째, 요약테이블에서 CNT가 50인 아이들을 조회

WHERE CNT = (SELECT MAX(CNT) FROM K)

셋째, 요약테이블에서 CNT가 중복되지 않는 ROW들 조회

SELECT CNT FROM K 
GROUP BY CNT HAVING COUNT(*) = 1

넷째, WHERE OR 을 이용해 쿼리 작성

WITH K AS (
SELECT H.hacker_id, H.name, COUNT(*) AS CNT
    FROM Challenges AS C
         INNER JOIN HACKERS AS H ON C.hacker_id = H.hacker_id
    GROUP BY H.hacker_id, H.name
)

SELECT 
K.hacker_id,
K.name,
CNT

FROM K

WHERE CNT = (SELECT MAX(CNT) FROM K)
    OR CNT IN (SELECT CNT FROM K GROUP BY CNT HAVING COUNT(*) = 1)
    
ORDER BY CNT DESC, K.hacker_id ASC;

다섯째, 검토 후 완성

 

 

GROUP BY 한 테이블을 다시 GROUP BY 해야 할 때, WITH AS ()가 유용함