[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.

요약하자면

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명일 때만 결과값 도출하라.)

그 외 조건들

1. Write a query to print the hacker_id, name, and the total number of challenges created by each student.
2. Sort your results by the total number of challenges in descending order.
3. If more than one student created the same number of challenges, then sort the result by hacker_id.

✍요구하는 것들을 각각 쪼개서 쿼리문으로 만들어 준 다음 마지막에 전부 합쳐줄 계획
1. 각각의 학생의 도전 횟수
2. 최댓값 구하기
3. 중복아닌 값 구하기

1. 각각의 학생의 도전 횟수

SELECT name, challenge_id, COUNT(*) 
FROM hackers h LEFT JOIN Challenges c
	ON h.hacker_id = c.hacker_id

GROUP BY challenge_id, name 

2. 최댓값 구하기

✍해커 아이디 별 도전 횟수부터 시작. MAX 이용해서 최댓값 구해줄 생각

2-1 해커 아이디별 도전 횟수

SELECT hacker_id, COUNT(*)
		FROM Challenges
		GROUP BY hacker_id

2-2 최대 도전 횟수 구해주기

SELECT MAX(max.co) AS Mmax

FROM (SELECT hacker_id,count(*) co
			FROM Challenges
			GROUP BY hacker_id) max

3. 중복아닌 값 구하기

3-1 도전횟수 구해보기

SELECT DIS.co
FROM (SELECT hacker_id, COUNT(*) co
			FROM Challenges
			GROUP BY hacker_id) DIS
GROUP BY DIS.co

3-2 도전 횟수를 수행한 학생이 1명인 값 출력(중복X 구하기)

SELECT DIS.co, COUNT(*)
FROM (SELECT hacker_id, COUNT(*) co
			FROM Challenges
			GROUP BY hacker_id) DIS
GROUP BY DIS.co
	HAVING COUNT(*) = 1 

4. 이제 마지막으로 전부 합쳐주기

SELECT name, challenge_id, COUNT(*) 
FROM hackers h LEFT JOIN Challenges c
	ON h.hacker_id = c.hacker_id

GROUP BY challenge_id, name  -- 각각의 학생의 도전 횟수
----------------------------
SELECT MAX(max.co) AS Mmax

FROM (SELECT hacker_id,count(*) co
			FROM Challenges
			GROUP BY hacker_id) max -- 최댓값 구하기
----------------------------------
SELECT DIS.co, COUNT(*)
FROM (SELECT hacker_id, COUNT(*) co
			FROM Challenges
			GROUP BY hacker_id) DIS -- 중복아닌 값 구하기
GROUP BY DIS.co
	HAVING COUNT(*) = 1 

최종 정답

SELECT h.name, h.hacker_id, COUNT(*) co
FROM hackers h LEFT JOIN Challenges c
	ON h.hacker_id = c.hacker_id

GROUP BY h.hacker_id, h.name
HAVING co = (SELECT MAX(maxx.co) AS Mmax
							FROM (SELECT hacker_id,count(*) co
										FROM Challenges
										GROUP BY hacker_id) maxx)

OR co IN (SELECT DIS.co
					FROM (SELECT hacker_id, COUNT(*) co
								FROM Challenges
								GROUP BY hacker_id) DIS
				GROUP BY DIS.co
				HAVING COUNT(*) = 1 )

ORDER BY co DESC, hacker_id

문제의 교훈
!쿼리가 복잡할수록 논리구조 하나하나 체계적으로 정리하며 풀기!
*풀이는 굉장히 간단해보이지만 4시간 걸린 문제...두들기다 보면 쿼리가 풀린다.