[SQL : WITH / 서브쿼리 / INNER JOIN] HackerRank : Challenges

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.

 

Input Format

The following tables contain challenge data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 

  

• Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge. 

  

---

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

Explanation

For Sample Case 0, we can get the following details:

Students  and  both created  challenges, but the maximum number of challenges created is  so these students are excluded from the result.

For Sample Case 1, we can get the following details:

Students  12299 and 34856 both created 6 challenges. Because  6 is the maximum number of challenges created, these students are included in the result.

 

>>  문제 설명 

SELETE : hacker_id, name, totla_number of challenges

SORT : 문제를 많이 만드는 사람을 위로 올리기

조건 

1. 한 명이상이 똑같은 Challenges 를 만들었다고하면 -> hacker_id 로 sort

2. maximum number of challenges 가 아니라면, 배제해줌

 

>> 문제 풀이 순서

1. hacker_id 별로 challenges를 센다

2. sorting 을 한다. desc

3. challenges 가 max가 아니고 중복이 있다면, 결과에서 제외를 해준다.

   challenges가  max이고 중복이 있다면, 결과에서 포함시켜준다.

 

 

>> 정답 코드

 

1. Hacker id , name 을 기준으로  그룹바이를 하여, count(*) 하고, INNER JOIN  , SORTING 

SELECT Hackers.hacker_id
    , Hackers.name
    , COUNT(*) AS challenges_created
FROM Challenges AS  CH1
    INNER JOIN Hackers on Hackers.hacker_id = CH1.hacker_id
GROUP BY Hackers.hacker_id, Hackers.name
ORDER BY challenges_created DESC, Hackers.hacker_id

 

2. Challenges_created 에 대한 조건을 해결해주기

  2.1. COUNT 한 것 중에 MAX 값을 구하기 -> FROM 서브 쿼리를 사용해야 한다.

SELECT MAX(challenges_created)
FROM (
    SELECT hacker_id,
        COUNT(*) AS challenges_created 
    FROM challenges
    Group by hacker_id ) sub1 
    
-- output : 50 

 2.2. COUNT 해서 나온 값을 다시 COUNT 하기 -> FROM 서브쿼리를 사용해야 한다.

SELECT challenges_created , COUNT(*)
FROM (
    SELECT hacker_id, count(*) as challenges_created
    FROM challenges
    GROUP BY hacker_id ) sub2
GROUP BY challenges_created

-- OUTPUT 
-- 1 126 
-- 2 128 
-- 3 71 
-- 4 24 
-- 5 10 

 여기서, COUNT 가 중복된 값은 없어져야 하기 때문에, 다시 HAVING 을 해준다.

SELECT challenges_created
FROM (
    SELECT hacker_id, count(*) as challenges_created
    FROM challenges
    GROUP BY hacker_id ) sub2
GROUP BY challenges_created
HAVING COUNT(*) = 1 

 

※ 전체 다 합친 코드

SELECT Hackers.hacker_id
    , Hackers.name
    , COUNT(*) AS challenges_created
FROM Challenges 
    INNER JOIN Hackers on Hackers.hacker_id = Challenges.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 ) sub1 
                            )
        OR challenges_created IN ( 
                                SELECT challenges_created 
                                FROM (
                                    SELECT hacker_id, count(*) as challenges_created
                                    FROM challenges
                                    GROUP BY hacker_id 
                                    ) sub2
                                GROUP BY challenges_created
                                HAVING COUNT(*) = 1 
                                ) 
ORDER BY challenges_created DESC, hacker_id

 

※ WITH 문을 사용하여 구한 코드

: 위의 코드를 보면, COUNT(*)을 해주는 부분에서 반복된다. 따라서, WITH를 사용하여 반복을 줄인다.

 이미 COUNTER 라는 WITH 문에 그룹바이까지 했기 때문에, HAVING 이 아닌, WHERE 절에 서브쿼리 조건을 넣어준다.

-- with 문 : 중복 / 반복 된 쿼리를 하나의 테이블 처럼 만들 수 있는 코드 , 재사용이 가능함 ( 함수 같은 아이 )

WITH COUNTER AS (
    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

)

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

 

 

www.hackerrank.com/challenges/challenges/problem?h_r=internal-search 

Challenges | HackerRank