[문제]
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' 은 서브 쿼리의 출력 결과가 여러 개일 때 사용한다.
'Test > MYSQL' 카테고리의 다른 글
184. Department Highest Salary (INNER JOIN, 서브쿼리, PARTITION BY) (0) | 2023.06.18 |
---|---|
180. Consecutive Numbers (WITH/AS, LEAD) (0) | 2023.06.18 |
196. Delete Duplicate Emails (DELETE, NOT IN, 서브쿼리) (0) | 2023.06.14 |
627. Swap Salary (UPDATE /SET, IF) (0) | 2023.06.12 |
Monthly Transactions I (DATE_FORMAT, CASE WHEN) (0) | 2023.05.28 |