[MySQL/HackerRank] Challenges 문제 풀이

https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true 

Challenges | HackerRank

HakcerRank - Challenges문제

 

무슨 이유인지 HackerRank에서 MySQL에서는 with문이 안돼서 FROM 절에서 서브쿼리는 넣는 방식으로 풀었다.

 

우선,

1. challeges_cnt( 도전 횟수) 가 MAX 값 이거나,
2. challeges_cnt( 도전 횟수) 가  중복값이 없는 것만 가져와야한다.

 

# 1번 쿼리 - challenge_cnt가 MAX인 값만 가져오기

SELECT MAX(t1.challenges_cnt) 
FROM (SELECT COUNT(*) as challenges_cnt
		FROM challenges c1
    	INNER JOIN hackers h1 ON c1.hacker_id = h1.hacker_id
    	GROUP BY h1.hacker_id) t1

 

#2번 쿼리 - COUNT(challenge_cnt)가 1인 데이터만 가져오기

SELECT challenges_cnt
FROM (select count(*) as challenges_cnt
      from challenges c1
      INNER JOIN hackers h1 ON c1.hacker_id = h1.hacker_id
      GROUP BY h1.hacker_id) as t2
 GROUP BY challenges_cnt
 HAVING COUNT(challenges_cnt) = 1

 

SELECT t.hacker_id, t.name, t.challenges_created
FROM (SELECT h.hacker_id, h.name, COUNT(*) AS challenges_created
    FROM challenges c
    INNER JOIN hackers h ON c.hacker_id = h.hacker_id
    GROUP BY h.hacker_id, h.name) AS t
HAVING challenges_created = (select Max(t2.challenges_cnt) 
	FROM (select count(*) as challenges_cnt
    	  from challenges c1
    	  INNER JOIN hackers h1 ON c1.hacker_id = h1.hacker_id
    	  GROUP BY h1.hacker_id) as t2)
OR challenges_created IN (SELECT challenges_cnt
    FROM (select count(*) as challenges_cnt
          from challenges c1
          INNER JOIN hackers h1 ON c1.hacker_id = h1.hacker_id
          GROUP BY h1.hacker_id) as t3 
    GROUP BY challenges_cnt
    HAVING COUNT(challenges_cnt) = 1)
ORDER BY t.challenges_created DESC, t.hacker_id