[HackerRank][SQL(Oracle)] Contest Leaderboard

 

※  The following tables contain contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker. 
• Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.

 

■  [Basic Join]  Contest Leaderboard

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

Q.

You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of  from your result.

A.

SELECT
	H.HACKER_ID
	,H.NAME
	,SUM(MAX_SCORE) AS SUM_MAX_SCORE
FROM
	HACKERS H
	,(
		SELECT
			HACKER_ID
			,CHALLENGE_ID
			,MAX(SCORE) AS MAX_SCORE
		FROM
			SUBMISSIONS
		WHERE
			SCORE > 0	-- SCORE <> 0
		GROUP BY
			HACKER_ID
			,CHALLENGE_ID
--		HAVING
--			MAX(SCORE) > 0
	) S_FL
WHERE
	H.HACKER_ID = S_FL.HACKER_ID
GROUP BY
	H.HACKER_ID
	,H.NAME
-- HAVING
--	SUM(MAX_SCORE) > 0
ORDER BY
	SUM(MAX_SCORE) DESC
	,H.HACKER_ID;

SELECT
	HACKER_ID
	,NAME
	,SUM(MAX_SCORE) AS SUM_MAX_SCORE
FROM
	(
		SELECT
			H.HACKER_ID
			,NAME
			,CHALLENGE_ID
			,MAX(SCORE) AS MAX_SCORE
		FROM
			SUBMISSIONS S JOIN HACKERS H 	ON S.HACKER_ID = H.HACKER_ID
		WHERE
			SCORE > 0
		GROUP BY
			H.HACKER_ID
			,NAME
			,CHALLENGE_ID
	)
GROUP BY
	HACKER_ID
	,NAME
ORDER BY
	SUM(MAX_SCORE) DESC
	,HACKER_ID;

WITH S_FL AS
(
	SELECT
		HACKER_ID
		,CHALLENGE_ID
		,MAX(SCORE) AS MAX_SCORE
	FROM
		SUBMISSIONS
	WHERE
		SCORE > 0
	GROUP BY
		HACKER_ID
		,CHALLENGE_ID
)
SELECT
	H.HACKER_ID
	,NAME
	,SUM(MAX_SCORE) AS SUM_MAX_SCORE
FROM
	HACKERS H
	,S_FL
WHERE
	H.HACKER_ID = S_FL.HACKER_ID
GROUP BY
	H.HACKER_ID
	,NAME
ORDER BY
	SUM(MAX_SCORE) DESC
	,H.HACKER_ID;