Challenges(WITH AS , 서브쿼리, NOT IN)

[문제]

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. 

 

 

Sample Input 0

Hackers Table: 

 Challenges Table: 

 

Sample Output 0

21283  Angela  6
88255  Patrick  5
96196  Lisa      1

 

 

 

[문제 링크]

https://www.hackerrank.com/challenges/challenges

 

 

---

[문제 설명 및 풀이 방법]

1. 해커 아이디(hacker_id)별로 도전한 문제(challenges)의 개수를 세어 테이블 만들기

2. 도전한 문제(challenges)의 개수는 내림차순, 해커 아이디(hacker_id)는 오름차순으로 정렬하기

3. 도전한 문제(challenges)의 개수가 가장 큰 값을 출력하기

4. 도전한 문제(challenges)의 개수가 가장 큰 값이 아니라면

    도전한 문제(challenges)의 개수가 중복인 경우 출력에서 제외하기  

 

 

[나의 풀이]

-- 1. 해커 아이디(hacker_id)별로 도전한 문제(challenges) 개수를 세어 테이블 만들기
WITH Challenges_cnt AS
(SELECT hacker_id, COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id
)
    
SELECT h.hacker_id 
     , h.name 
     , c.cnt
FROM Hackers h
    INNER JOIN Challenges_cnt c ON c.hacker_id = h.hacker_id
-- 3. 도전한 문제(challenges)의 개수가 가장 큰 값을 출력하기
WHERE c.cnt = (SELECT MAX(cnt) FROM Challenges_cnt)
-- 4. 도전한 문제(challenges)의 개수가 가장 큰 값이 아니라면
--    도전한 문제(challenges)의 개수가 중복인 경우 출력에서 제외하기  
    OR c.cnt NOT IN (SELECT cnt
                        FROM Challenges_cnt
                        GROUP BY cnt
                        HAVING COUNT(*) > 1)
-- 2. 도전한 문제(challenges)의 개수는 내림차순, 
--    해커 아이디(hacker_id)는 오름차순으로 정렬하기
ORDER BY c.cnt DESC, h.hacker_id

---

[설명]

1. 해커 아이디(hacker_id)별로 도전한 문제(challenges) 개수를 세어 테이블 만들기

WITH Challenges_cnt AS
(SELECT hacker_id, COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id
)

SELECT *
FROM Challenges_cnt

출력 결과

hacker_id	cnt
46963	31
26332	3
: :	: :
17311	1

> WITH 뒤에 나오는 Challenges_cnt는 새로 만든 테이블의 이름이다.

 

 

2. 도전한 문제(challenges)의 개수는 내림차순
    해커 아이디(hacker_id)는 오름차순으로 정렬하기

WITH Challenges_cnt AS
(SELECT hacker_id, COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id
)

SELECT h.hacker_id 
     , h.name 
     , c.cnt
FROM Hackers h
    INNER JOIN Challenges_cnt c ON c.hacker_id = h.hacker_id
ORDER BY c.cnt DESC, h.hacker_id

출력 결과

h.hacker_id	h.name	c.cnt
5120	Julia	50
18425	Anna	50
: :	: :	: :
99165	Nancy	1

 

 

3. 도전한 문제(challenges)의 개수가 가장 큰 값 출력하기

WITH Challenges_cnt AS
(SELECT hacker_id, COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id
)

SELECT h.hacker_id 
     , h.name 
     , c.cnt
FROM Hackers h
    INNER JOIN Challenges_cnt c ON c.hacker_id = h.hacker_id
WHERE c.cnt = (SELECT MAX(cnt) FROM Challenges_cnt)
ORDER BY c.cnt DESC, h.hacker_id

출력 결과

h.hacker_id	h.name	c.cnt
5120	Julia	50
18425	Anna	50
: :	: :	: :
96716	Emily	50

> WHERE c.cnt = (SELECT MAX(cnt) FROM Challenges_cnt)

WHERE 뒤에 '컬럼 ='는 서브 쿼리의 출력 결과가 한 개일 때 사용한다.

 

 

4. 도전한 문제(challenges) 개수가 가장 큰 값이 아니라면   
    도전한 문제(challenges) 개수가 중복인 경우 출력에서 제외하기  

WITH Challenges_cnt AS
(SELECT hacker_id, COUNT(*) AS cnt
FROM Challenges
GROUP BY hacker_id
)

SELECT h.hacker_id 
     , h.name 
     , c.cnt
FROM Hackers h
    INNER JOIN Challenges_cnt c ON c.hacker_id = h.hacker_id
WHERE c.cnt = (SELECT MAX(cnt) FROM Challenges_cnt)
    OR c.cnt NOT IN (SELECT cnt
                        FROM Challenges_cnt
                        GROUP BY cnt
                        HAVING COUNT(*) > 1)
ORDER BY c.cnt DESC, h.hacker_id

출력 결과

h.hacker_id	h.name	c.cnt
5120	Julia	50
18425	Anna	50
: :	: :	: :
90267	Edward	9
72609	Bobby	8

> OR c.cnt NOT IN (SELECT cnt ...)

WHERE절의 '컬럼 NOT IN' 또는 '컬럼 IN' 은 서브 쿼리의 출력 결과가 여러 개일 때 사용한다.