HackerRank > Challenges

Jihyun Park·2020년 9월 6일
0
post-thumbnail

Practice > SQL > Basic Join > Challenges

Problem

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.
문제링크

Answer

-- 이 문제는 조금 어려움
-- 1. id 별로 #challenges 수를 센다
-- 1-1. #C = MAX(C) >> Include
-- 1-2. #C ! MAX(C) and 중복 >> Exclude
-- 2. 1-1의 경우에는 Hacker_id를 기준으로 DESC Sorting

-- 출력할 것: hacker_id, name, challenges_created
-- Sort: challenges_created DESC, hacker_id

SELECT h.hacker_id 
     , h.name
     , COUNT(*) AS challenges_created
FROM hackers as h
     INNER JOIN challenges as c ON h.hacker_id=c.hacker_id
GROUP BY h.hacker_id, h.name
-- 여기까지가 기본 골자 + 여기에 조건이 두개 더 들어가야함.
-- GROUP BY의 결과물을 가지고 조건을 만들어야 하기 때문에 GROUP BY 아래 HAVING절을 써야함
HAVING challenges_created = (SELECT MAX(challenges_created) -- () 꼭 써주기, 1개이기 때문에 =으로 연결함
                            FROM(
                                SELECT COUNT(*) AS challenges_created
                                FROM Challenges
                                GROUP BY hacker_id
                                ) AS sub) -- 가장 큰 값(50)은 포함, From에 서브쿼리

-- 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로 연결해야함.
OR challenges_created IN (SELECT challenges_created -- 2개 이상이기 때문에 IN으로 연결
                          FROM(
                              SELECT COUNT(*) AS challenges_created
                              FROM Challenges
                              GROUP BY hacker_id
                              ) sub
                              GROUP BY challenges_created
                              HAVING COUNT(*) = 1)
ORDER BY challenges_created DESC, h.hacker_id

0개의 댓글