[해커랭크(HackerRank)] Challenges (MySQL)

문제

• 줄리아는 학생들에게 코딩 과제를 만들라고 요청했다
• hacker_id, 이름 및 각 학생이 낸 총 과제 수를 출력한다
• 총 과제 수를 기준으로 내림차순으로 정렬한다
• 한 명 이상의 학생이 동일한 수의 과제를 낸 경우 hacker_id 별로 결과를 정렬한다
• 한 명 이상의 학생이 동일한 수의 도전 과제를 냈고 그 수가 최대 도전 횟수보다 적다면, 그 학생들은 결과에서 제외한다
• Hackers 테이블 : hacker_id와 name
• Challenges 테이블 : 도전한 id인 challenge_id와 문제를 만든 학생의 id인 hacker_id

코드

1. 각 학생이 낸 총 과제 수 출력

SELECT h.hacker_id, h.name, COUNT(*)
FROM hackers h
     INNER JOIN challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name

2. 내림차순으로 정렬, 한 명 이상의 학생이 동일한 수의 과제를 낸 경우 hacker_id 별로 결과를 정렬

SELECT h.hacker_id, h.name, COUNT(*)
FROM hackers h
     INNER JOIN challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
ORDER BY cnt DESC, h.hacker_id

3. 최대 도전 횟수 구하기

SELECT max(sub.cnt) as maxcnt
FROM (
    SELECT hacker_id, count(*) cnt
    FROM challenges
    GROUP BY hacker_id
) sub

4. 중복되지 않는 도전 횟수 구하기

한 명 이상의 학생이 동일한 수의 도전 과제를 냈고 그 수가 최대 도전 횟수보다 적다면, 그 학생들은 결과에서 제외한다

SELECT sub.cnt, count(*)
FROM (
    select hacker_id, count(*) cnt
    from challenges
    group by hacker_id
) sub
GROUP BY sub.cnt
HAVING count(*) = 1

5. 2번째 쿼리에서 HAVING에 3, 4번을 포함하는 cnt 조건 주기

alias를 WHERE 조건에서 주면 안되기 때문에 집계함수 조건인 HAVING에서 해당 조건 작성!

SELECT h.hacker_id, h.name, COUNT(*) cnt
FROM hackers h
     INNER JOIN challenges c ON h.hacker_id = c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING cnt = (SELECT max(sub.cnt) as maxcnt
                FROM (
                    SELECT hacker_id, count(*) cnt
                    FROM challenges
                    GROUP BY hacker_id
                ) sub)
OR cnt IN (SELECT sub.cnt
                FROM (
                    select hacker_id, count(*) cnt
                    from challenges
                    group by hacker_id
                ) sub
                GROUP BY sub.cnt
                HAVING count(*) = 1)
ORDER BY cnt DESC, h.hacker_id

WITH 문

• WITH 문을 작성하면 여러 번 재사용이 가능하다
• 코드를 깨끗하고 구조적으로 풀 수 있다

WITH counter AS (
	SELECT hackers.hacker_id, hackers.name, COUNT(*) AS cnt
    FROM Challenges
    	 INNER JOIN hackers ON challenges.hacker_id = Hackers.hacker_id
    GROUP BY hackers.hacker_id, hackers.name
)

SELECT counter.hacker_id
	 , counter.name
     , counter.challenges_created
FROM counter
WHERE cnt = (SELECT MAX(cnt) FROM counter)
OR cnt IN (SELECT cnt FROM counter GROUP BY cnt HAVING count(*) = 1)
ORDER BY counter.cnt DESC, counter.hacker_id

출처