인프런 - SQL 고급 강의 듣고 공부 03

[hdonghun]

HackerRank - Challenges 문제

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. Sample Input 0 Hackers Table: Challenges Table:

Sample Output 0

21283 Angela 6 88255 Patrick 5 96196 Lisa 1

Sample Input 1

Hackers Table: Challenges Table:

Sample Output 1

12299 Rose 6 34856 Angela 6 79345 Frank 4 80491 Patrick 3 81041 Lisa 1

출처 : https://www.hackerrank.com/challenges/challenges/problem?h_r=internal-search

[hdonghun]

MySQL 답안 : 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 HAVING challenges_created = (SELECT MAX(challenges_created) FROM( SELECT hacker_id , COUNT() as challenges_created FROM Challenges Group by hacker_id ) sub) 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

[hdonghun]

MSSQL 답안 👍🏻 : 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