[SQL Runday] HackerRank - Challenges

줄리아는 학생들에게 코딩 챌린지들을 만들라고 지시했다.

• hacker_id, name, 각 학생들이 만든 코딩 챌린지 갯수를 출력하는 쿼리를 작성해라.
• 총 갯수에 따라 내림차순으로 정렬
• 갯수가 같다면, hacker_id 기준으로 정렬
• 한 명 이상의 학생이 같은 갯수의 챌린지를 작성하고, 갯수가 maximum number of challenges created 보다 작다면, 해당 학생들을 제외하라.

1. hacker_id, name, challenge_id를 2개의 테이블을 Join해서 가져온다.
2. group by를 통해 인당 만든 갯수를 구한다.
3. max(challenge_id) 값을 따로 구한다.
4. 만일 count(challenge_id) 값이 같을 경우, max보다 작을때 조회하지 않는다.

Key points

필요한 서브 테이블

1. 각 해커당 만든 챌린지 갯수의 빈도 테이블
   • 4번 조건을 만족하기 위해 필요한 테이블
   • ex) 50개 - 12, 41개 - 1, 5개 - 10, 1개 - 126
   • 해당 테이블에서 빈도 1만 선택한다.
   • 메인 테이블에서 해당 테이블에 있는 빈도만 불러온다!
     
2. 각 해커당 만든 챌린지 갯수 Max값
   • 위의 counted_table에서 max(counted) 값을 가져온다.
   • 이 때 max, min 값을 가져올땐 group by 하지말것 유의.
   • 여기서는 값이 50임

Errors

1. having count(*) in 이렇게 쓸 수 있다. 필터링 조건에 >, <, = 뿐만 아니라 IN 도 쓸 수 있음.
2. 처음 작성했던 쿼리

select h.hacker_id, h.name, count(*)
from Hackers h
join Challenges c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having count(*) in (select counted from (
                        select count(*) as counted
                        from Hackers h
                        join Challenges c on h.hacker_id = c.hacker_id
                        group by h.hacker_id)counted_table
                        group by counted
                        having count(*) = 1)
or chal_count = (select max(counted) from (
                    select count(*) as counted            
                    from Hackers h
                    join Challenges c on h.hacker_id = c.hacker_id
                    group by h.hacker_id)counted_table)
order by chal_count DESC, h.hacker_id

• 이렇게 하니까 0개의 레코드가 조회되었다. in절 이후가 문제란 뜻.
• 무슨 문젠지 고민해봤는데, 저 count() 로 작성한 것이 문제였다. 저렇게 지정하면 count()가 내가 원하는 대로 조회하지 않고, 겹쳐서(?) 각각으로 여겨지지 않는듯하다.
• 따라서 최대한 count(*) 식으로 쓰는 걸 지양하고, alias를 쓰는 것을 지향해야 한다.

최종 쿼리

select h.hacker_id, h.name, count(*) as chal_count
from Hackers h
join Challenges c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having chal_count in (select counted from (
                        select count(*) as counted
                        from Hackers h
                        join Challenges c on h.hacker_id = c.hacker_id
                        group by h.hacker_id)counted_table
                        group by counted
                        having count(counted) = 1)
or chal_count = (select max(counted) from (
                    select count(*) as counted            
                    from Hackers h
                    join Challenges c on h.hacker_id = c.hacker_id
                    group by h.hacker_id)counted_table)
order by chal_count DESC, h.hacker_id

chal_count로 지정하여 having에 넣었더니 잘 조회된다.

• 드는 의문점)
  • 보면 counted_table이 중복되고 있는데, 저 위 서브쿼리에서 사용한 counted_table을 아래 서브쿼리에서 또 사용할 수 없을까?