링크

https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true

문제 설명

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.

코드

SELECT h.hacker_id, h.name, COUNT(*) AS counted FROM Hackers h
INNER JOIN Challenges c ON h.hacker_id=c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING
    counted=(
        SELECT MAX(cnt) FROM(
            SELECT COUNT(ch.challenge_id) AS cnt FROM Challenges ch
            INNER JOIN Hackers ha ON ch.hacker_id=ha.hacker_id
            GROUP BY ha.hacker_id
        )AS max_count
    )OR
    counted in(
        SELECT cnt1 FROM(
            SELECT COUNT(cha.challenge_id)AS cnt1 FROM Challenges cha
            INNER JOIN Hackers hac ON hac.hacker_id=cha.hacker_id
            GROUP BY hac.hacker_id
        )AS alias_cnt
        GROUP BY cnt1
        HAVING COUNT(cnt1)=1
    )
ORDER BY counted desc, h.hacker_id;

해설

1. 구조를 짜자.
   문제를 읽어보면, 최대 개수이거나 개수의 개수가 1개이면 쿼리를 포함한다.

SELECT h.hacker_id, h.name, COUNT(*) AS counted FROM Hackers h
INNER JOIN Challenges c ON h.hacker_id=c.hacker_id
GROUP BY h.hacker_id, h.name
HAVING counted=() OR counted in ()
ORDER BY counted desc, h.hacker_id;

최대 개수의 경우 결괏값이 1개이므로 =을 사용한다. 반면 개수의 개수가 1개인 것들의 결과는 여러개이므로 in을 사용한다.

2. 최대 개수를 구하자.

SELECT MAX(cnt) FROM(
            SELECT COUNT(ch.challenge_id) AS cnt FROM Challenges ch
            INNER JOIN Hackers ha ON ch.hacker_id=ha.hacker_id
            GROUP BY ha.hacker_id)AS max_count

MySql의 문법 상 집계 함수끼리는 중첩할 수 없다. 따라서 max(count))는 잘못된 문법이다. 올바른 문법은 위 코드처럼 작성해야 한다. 주의할 점은 FROM () AS 별칭처럼 서브쿼리에 별칭을 꼭 해줘야 한다.

3. 개수의 개수가 1인 것을 구하자.
   개수의 개수라서 생각해내기 힘들었던 부분이다. 다른 해설을 많이 참고했다.

        SELECT cnt1 FROM(
            SELECT COUNT(cha.challenge_id)AS cnt1 FROM Challenges cha
            INNER JOIN Hackers hac ON hac.hacker_id=cha.hacker_id
            GROUP BY hac.hacker_id
        )AS alias_cnt
        GROUP BY cnt1
        HAVING COUNT(cnt1)=1

참고

https://ysyblog.tistory.com/202

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=jinsol1&logNo=100025031674