HackerRank SQL Challenges (Basicjoin)

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

• 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 Input0

• Hackers Table :
  

• Challengers Table :
  

Sample Output0

Sample Input1

• Hackers Table :
  

• Challenges Table :
  

Sample Output1

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 and both created challenges. Because is the maximum number of challenges created, these students are included in the result.

풀이 (mySQL)

이 문제의 경우 문제에 해당하는 조건을 작성하는 부분이 꽤 까다로웠다.

우선을 기본적으로 hacker_id와 name을 기준으로 GROUP BY를 해주는 과정은 일반적인 쉬운 예제와 동일하다. 이에 추가로 총 챌린지의 수를 count() 집계함수로 표현하는 부분 및 최종적으로 계산한 tot_num에 대해 내림차순, hacker_id에 대해 오름차순으로 정렬하는 부분은 큰 문제가 없었다.

SQL 코드의 경우 특정 문자열등의 케이스를 제외하고 대소문자를 구분하지 않기 때문에 소문자로 적었다.

select h.hacker_id, h.name, count(*) as tot_num
from hackers h join challenges c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
[조건절]
order by tot_num desc, h.hacker_id

기본적으로 having으로 시작하는 조건절을 만들 경우 위의 select~ from~ group by~ order by~ 가 이루어진 후에 필터의 역할로 기능하기 때문에 having을 통해 문제의 조건을 고려해줄 수 있다.

"두명 이상의 학생이 동일한 수의 도전 과제를 생성했고 개수가 생성된 최대 도전 과제 수보다 적은 경우 해당 학생을 결과에서 제외한다"라는 부분이 있는데

1) 두 명 이상의 학생이 동일한 수의 도전 과제를 생성
2) 생성한 개수가 최대 도전 과제수보다 적다

위의 두 조건인 경우 결과에서 제외를 하는 것이라면

위의 두 조건을 제외한 나머지 경우를 결과에 포함해야 한다는 의미로 이해할 수 있다.

즉, 다시 바꾸어 이해해보면

1) 생성한 개수가 최대 도전 과제수인 경우
혹은
2) 최대 도전 과제수보다 작은데 개수가 유일한 경우

위의 두 조건을 OR로 고려한 결과를 출력해야 한다.

기본적으로 위 경우는 tot_num이 만족해야 하는 조건들이다.

1. 최대 도전 과제수를 조회

select count(*)
from challenges
group by hacker_id
order by count(*) desc limit 1

2. 챌린지 개수가 유일한 경우 : 챌린지 개수를 결과로 조회하는 것이 목적이고, 조건을 활용해 tot_num의 개수가 한 종류만 존재하는 경우만을 선택해야 한다.

select tot_num
from (select count(*) as tot_num
from challenges
group by hacker_id) as tot
group by tot_num
having count(tot_num) = 1

즉, tot_num이라고 부르기로 한 총 챌린지 수가 1번 조건의 경우 1번에서 조회한 count(*)와 같은 경우 tot_num이 최대 도전 과제수인 것이고

2번 조건의 경우 unique한 챌린지 개수들 중에 tot_num이 존재하는 지 확인하면 된다.

having tot_num = ( 
    select count(*)
    from challenges
    group by hacker_id
    order by count(*) desc limit 1)
or
tot_num in (
	select tot_num
    from (select count(*) as tot_num
    from challenges
    group by hacker_id) as tot
    group by tot_num
    having count(tot_num) = 1)

따라서 정답은 아래와 같다.

Answer

select h.hacker_id, h.name, count(*) as tot_num
from hackers h join challenges c on h.hacker_id = c.hacker_id
group by h.hacker_id, h.name
having tot_num = ( 
    select count(*)
    from challenges
    group by hacker_id
    order by count(*) desc limit 1)
or
tot_num in (
	select tot_num
    from (select count(*) as tot_num
    from challenges
    group by hacker_id) as tot
    group by tot_num
    having count(tot_num) = 1)
order by tot_num desc, h.hacker_id;