Hackerrank | MySQL | Challenges

Challenges

Problem

Challenges | HackerRank

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.

 

 

-- 출력 hackers.hacker_id, hackers.name, challenges_created
-- sort challenges_created DESC, hacker_id

SELECT hackers.hacker_id
        , hackers.name
        , COUNT(*) challenges_created
FROM Challenges
    INNER JOIN Hackers On Challenges.hacker_id = Hackers.hacker_id
GROUP BY hackers.hacker_id, hackers.name
-- MAX(challenges_created), example 50
HAVING challenges_created = (SELECT MAX(challenges_created)
                            FROM(
                                SELECT hacker_id
                                        , COUNT(*)
                                FROM Challenges
                                GROUP BY hacker_id) sub)
-- 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.
OR challenges_created IN (SELECT challenges_created
                         FROM (
                             SELECT hacker_id
                                , COUNT(*) AS challenges_created
                             FROM Challenges
                             GROUP BY hacker_id)sub)
                         GROUP BY challenges_created
                         HAVING COUNT(*) = 1)
ORDER BY challenges_created DESC, hacker_id

 

#WITH 써서 하기

WITH counter AS(
SELECT hackers.hacker_id
, hackers.name
, COUNT(*) AS challenges_created
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 challenges_created = (SELECT MAX(challenges_created) FROM counter)
OR challenges_created IN (SELECT challenges_created
                         FROM counter
                         GROUP BY challenges_created
                         HAVING COUNT(*) = 1)
ORDER BY counter.challenges_created DESC, counter.hacker_id