[Inflearn] SQL 강의 고급 [2] Subquery

[Hacker Rank] 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.

ㄴ 조건1) #challenges != #max(challenges) -> exclude
ㄴ 조건2) #challenges = #max(challenges) -> include

[ 내가 처음 작성한 답 ]

select h.hacker_id, h.name, count(c.challenge_id) as total
from hackers h 
inner join challenges c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having total =  (select * from ) -- 여기서 막힘 ㅜㅜ 
order by total desc, h.hacker_id


1) hacekr_id 별로 #chanllenges를 센다
2) sorting은 #challenges desc
3) #challenges가 =! #max(challenges) 
그리고 #challenges 중복이 있다면 > 결과에서 제외 

*) 내가 헷갈리는 부분: 3번 부분

☑️ [풀이1]

SELECT H.HACKER_ID,
        H.NAME,
        COUNT(*) AS CHALLENGES_CREATED
FROM CHALLENGES C
	INNER JOIN HACKERS H ON C.HACKER_ID = H.HACKER_ID
GROUP BY H.HACKERS_ID, H.NAME
HAVING CHALLENGES_CREATED = (SELECT MAX(CHALLENGES_CREATED)
							FROM (
					SELECT HACKER_ID, COUNT(*) AS CHALLENGES_CREATED
    				FROM CHALLENGES 
    				GROUP BY HACKER_ID ) SUB)



OR CHALLENGES_CREATED IN (SELECT CHALLENGES_CREATED, COUNT(*)
							FROM (
							SELECT HACKER_ID, COUNT(*) AS CHALLENGES_CREATED
							FROM CHALLENGES
							GROUP BY HACKER_ID 
                            ) SUB
							GROUP BY CHALLENGES_CREATED
							HAVING COUNT(*) = 1
ORDER BY CHALLENGES_CREATED DESC, HACKER_ID

🔸#challenges = #max(challenges) > INCLUDE 를 어떻게 쿼리로 작성할까?
해결1) 각 HACEKR_ID별로 CHALEENGES를 가운트한다.
SELECT HACKER_ID, COUNT(*) AS CHALLENGES_CREATED
FROM CHALLENGES 
GROUP BY HACKER_ID 

해결2) 각 CHALLENGES 카운트 값 중 가장 MAX값을 킵해둔다. 
SELECT MAX(CHALLENGES_CREATED)
FROM (
	SELECT HACKER_ID, COUNT(*) AS CHALLENGES_CREATED
    FROM CHALLENGES 
    GROUP BY HACKER_ID ) 


🔸#challenges != #max(challenges) -> exclude 쿼리 작성
예를 들어 #CHALEENGES 수 카운트를 했더니 12가 3명이면 그 COUNT가 1인 사람만 추출 

SELECT CHALLENGES_CREATED, COUNT(*)
FROM (
	SELECT HACKER_ID, COUNT(*) AS CHALLENGES_CREATED
	FROM CHALLENGES
	GROUP BY HACKER_ID ) SUB
GROUP BY CHALLENGES_CREATED
HAVING COUNT(*) = 1

☑️ [풀이2]

WITH COUNTER AS (
	SELECT H.HAKCER_ID, H.NAME, COUNT(*) AS CHALLENGES_CREATED
    FROM CHALLNEGES C
    	INNER JOIN HACKERS H ON C.HACKER_ID = H.HACKER_ID
    GROUP BY H.HACKER_ID, H.NAME
 )
 
SELECT COUNTER.HACKER_ID, COUNTER.NAME, COUNTER.CHALLENGES_CREATED
FROM COUNTER
WHERE CHLEENGES_CREATED =(SELECT MAC(CHALLENGES_CREATED) FROM COUNTER)
OR CHALLENGES_CREATED IN (SELECT CHALLENGES_CREADTED FROM COUNTER 
						GROUP BY CHALLENGE_CREATED 
                        HAVING COUNT(*) = 1)
ORDER BY COUNTER.CHALLENGES_CREATED DESC, COUNTER.HACKER_ID