[HackerRank][SQL(Oracle)] Challenges

 

※  The following tables contain challenge data:

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

 

■  [Basic Join]  Challenges

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

Q.

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.

A.

WITH HC AS (
	SELECT 		ID, NAME, CNG_CNT
	FROM 		HACKERS H JOIN (
					SELECT		HACKER_ID AS ID
								,COUNT(*) AS CNG_CNT
					FROM 		CHALLENGES 
					GROUP BY 	HACKER_ID
				) C_FL	ON	H.HACKER_ID = C_FL.ID
	ORDER BY 	CNG_CNT DESC, ID
)
SELECT		*
FROM		HC
WHERE 		CNG_CNT NOT IN ( 					-- IN
				SELECT 		CNG_CNT
				FROM 		HC
				GROUP BY 	CNG_CNT
				HAVING 		COUNT(*) > 1 AND 	-- = 1 OR
							CNG_CNT < ( 		-- =
								SELECT	MAX(CNG_CNT) AS MAX_CNG_CNT
								FROM	HC
							)
			);

(+)

WITH HC AS (
	SELECT 		HACKER_ID AS ID
				,NAME
				,COUNT(*) AS CNG_CNT
				,MAX(COUNT(*)) OVER () AS MAX_CNG_CNT
	FROM 		HACKERS JOIN CHALLENGES USING(HACKER_ID)
	GROUP BY 	HACKER_ID, NAME
), HC2 AS (
	SELECT 		CNG_CNT
				,COUNT(*) AS HKR_CNT
	FROM 		HC
	GROUP BY 	CNG_CNT
)
SELECT 		ID, NAME, CNG_CNT
FROM 		HC JOIN HC2 USING (CNG_CNT)
WHERE 		HKR_CNT = 1 OR CNG_CNT = MAX_CNG_CNT
ORDER BY 	CNG_CNT DESC, ID;