[SQL] HackerRank - Challenges(M)

   Challenges  

Challenges | HackerRank

 

---

# 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.

 

Input Format

The following tables contain challenge data:

 

Sample Input 0, 1

 

Sample Output 0, 1

 

Explanation

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

Students  5077 and  62743 both created  4 challenges, but the maximum number of challenges created is 6 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.

 

---

# Answer

1) hacker_id, name, total number of challenges 출력하기
2) total number 순으로 내림차순, hacker_id 순으로 오름차순 정렬하기
3) total number가 같은 학생이 2명 이상인데, 그 값이 total의 최댓값보다 작다면 출력에서 제외하기

 

이 문제는 서브쿼리를 사용해야 한다.

3번을 만족하려면 total number는 다른 학생들과 중복되지 않는 고유값이거나 최댓값이어야 한다.

이에 대해 단계별로 쿼리를 작성해보자

 

 

1. challenges_created가 중복되지 않고 고유한 값인 경우

 

먼저, hacker_id로 그룹화해 학생별 challenges 수를 구해보면 다음과 같다.

SELECT c.hacker_id, COUNT(*) AS total
  FROM challenges AS c
 GROUP BY c.hacker_id

아이디가 10, 91, 158, 1315인 학생은 모두 challenges 수가 2이고 최댓값이 아니므로 출력에서 제외돼야한다.

 

 

고유값을 찾으려면 위의 쿼리를 다시 total을 기준으로 그룹화 후 COUNT한 값이 1이어야 한다.

SELECT A.total
  FROM (SELECT c.hacker_id, COUNT(*) AS total
          FROM challenges AS c
         GROUP BY c.hacker_id) AS A
 GROUP BY A.total
HAVING COUNT(*) = 1

• 8, 9, 10 등의 total number of challenges는 모두 중복되지 않는고유한 값이다.

 

 

2. challenges_created가 최댓값인 경우

SELECT MAX(B.total)
  FROM (SELECT c.hacker_id, COUNT(*) AS total
          FROM challenges AS c
         GROUP BY c.hacker_id) AS B

서브쿼리는 위와 동일하고 이번에는 최댓값을 출력해야 하므로 SELECT절에 MAX()를 사용해주었다.

 

 

 이제 전체 쿼리를 작성해주자!

(1) Hackers, Challenges 테이블을 hacker_id 기준으로 LEFT JOIN 해주고 hacker_id와 name으로 그룹화하였다.

 

(2) 출력해야하는 hacker_id, name, challenges_created를 SELECT절에 작성해주었는데 

이 때 challenges_created는 위의 두 경우 중 한 가지에 해당해야 한다.

따라서 이 조건을 HAVING절에 추가해주었다.

 

(3) 마지막으로 challenges_created 기준으로 내림차순 DESC, hacker_id 기준으로 오름차순 정렬을 해주었다.

SELECT h.hacker_id
     , h.name
     , COUNT(*) AS challenges_created
  FROM challenges AS c
  LEFT JOIN hackers AS h ON c.hacker_id = h.hacker_id
 GROUP BY h.hacker_id, h.name  -- (1)
HAVING challenges_created IN (SELECT A.total
                                FROM (SELECT c.hacker_id, COUNT(*) AS total
                                        FROM challenges AS c
                                       GROUP BY c.hacker_id) AS A
                               GROUP BY A.total
                              HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(B.total)
                               FROM (SELECT c.hacker_id, COUNT(*) AS total
                                       FROM challenges AS c
                                      GROUP BY c.hacker_id) AS B)  -- (2)
 ORDER BY challenges_created DESC, h.hacker_id;  -- (3)