[SQL] HackerLank : 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.

 

 

# 문제 구조화

 1. 구해야 하는 것

 :hacker_id, name, and the total number of challenges created by each student.

 2. 필터 조건

 :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.
3. 정렬 조건

:total number of challenges in descending order , hacker_id

 

 

풀이

 

WITH hc AS (      SELECT h.hacker_id                    ,h.name                    ,COUNT(*) AS challenges_created      FROM hackers h          INNER JOIN challenges c ON h.hacker_id = c.hacker_id      GROUP BY h.hacker_id                         ,h.name )      SELECT *  FROM hc   WHERE challenges_created NOT IN (                                                                SELECT challenges_created                                                                FROM hc                                                                GROUP BY challenges_created                                                                 HAVING COUNT(*) >= 2                                                                AND challenges_created < (SELECT MAX(challenges_created) FROM hc )                                                             )  ORDER BY challenges_created DESC                     ,hacker_id

- WITH문으로 hakers 테이블과 challenges 테이블 조인 

- WHERE절에서 서브쿼리 활용하여 조건 필터링

 

++) HAVING절에서는 집계함수 바로 사용 가능!

      GROUP BY에서 지정해 준 컬럼을 기준으로 그룹을 묶어 집계한 후 그 집계값에 조건을 걸어줄 때 사용함