1.質問

1.1表、フィールド情報

1.2問題の概要

2.解答（1）

-- (1) total 값이 최대인 학생
SELECT H.HACKER_ID, H.NAME, B.TOTAL
FROM (
    SELECT A.TOTAL, COUNT(A.TOTAL) TOTAL_COUNT
    FROM (
        SELECT HACKER_ID, COUNT(CHALLENGE_ID) TOTAL 
        FROM CHALLENGES
        GROUP BY 1
        ) A
    GROUP BY A.TOTAL
    ORDER BY 1 DESC
    LIMIT 1) B
JOIN (
    SELECT HACKER_ID, COUNT(CHALLENGE_ID) TOTAL
    FROM CHALLENGES
    GROUP BY 1
) C ON B.TOTAL = C.TOTAL
JOIN HACKERS H ON H.HACKER_ID=C.HACKER_ID
ORDER BY 3 DESC, 1;

--- (2) total 값이 유일한 학생
SELECT H.HACKER_ID, H.NAME, B.TOTAL
FROM (
    SELECT A.TOTAL, COUNT(A.TOTAL) TOTAL_COUNT
    FROM (
        SELECT HACKER_ID, COUNT(CHALLENGE_ID) TOTAL 
        FROM CHALLENGES
        GROUP BY 1
        ) A
    GROUP BY A.TOTAL
    HAVING TOTAL_COUNT = 1) B
JOIN (
    SELECT HACKER_ID, COUNT(CHALLENGE_ID) TOTAL
    FROM CHALLENGES
    GROUP BY 1
) C ON B.TOTAL = C.TOTAL
JOIN HACKERS H ON H.HACKER_ID=C.HACKER_ID
ORDER BY 3 DESC, 1;

2.1何か間違いがありますか

2.解答（2）

SELECT Hackers.hacker_id, Hackers.name, COUNT(*) AS challenges_created
FROM Hackers
INNER JOIN Challenges ON Hackers.hacker_id = Challenges.hacker_id
GROUP BY Hackers.hacker_id -- hacker id가 primary key역할을 한다면, name은 빼도 될 것 같다.
HAVING challenges_created IN (SELECT sub2.challenges_created
                              FROM (SELECT hacker_id, COUNT(*) AS challenges_created
                                    FROM Challenges
                                    GROUP BY Challenges.hacker_id) sub2
                              GROUP BY sub2.challenges_created
                              HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(sub1.challenges_created)
                             FROM (SELECT COUNT(*) AS challenges_created
                                   FROM Challenges
                                   GROUP BY Challenges.hacker_id) sub1)
ORDER BY challenges_created DESC, Hackers.hacker_id