[HackerRank][SQL(Oracle)] 15 Days of Learning SQL

 

※  The following tables hold contest data:

• Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
  
• Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.
  

 

■  [Advanced Select]  15 Days of Learning SQL

https://www.hackerrank.com/challenges/15-days-of-learning-sql/problem?isFullScreen=true 

Q.

Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
Write a query to print total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.

A.

(+)  ( 아래 조인 쿼리 )

SELECT 		SUBMISSION_DATE, CNT, HACKER_ID, NAME
FROM 		(
				SELECT 		SUBMISSION_DATE, HACKER_ID, NAME
				FROM 		HACKERS JOIN (
								SELECT 	ROW_NUMBER() OVER(PARTITION BY SUBMISSION_DATE ORDER BY SUBMISSION_DATE, CNT_S DESC, HACKER_ID) AS RN, T.*
								FROM 	(
									SELECT 		SUBMISSION_DATE, HACKER_ID, COUNT(*) AS CNT_S
									FROM 		SUBMISSIONS
									GROUP BY 	SUBMISSION_DATE, HACKER_ID
									-- HAVING 	COUNT(*) > 0
								) T
								WHERE CNT_S > 0
							) USING(HACKER_ID)
				WHERE 		RN = 1
			) JOIN (
				SELECT 		SUBMISSION_DATE, COUNT(DISTINCT HACKER_ID) AS CNT
				FROM 		SUBMISSIONS S
				WHERE 		EXISTS (
								SELECT 		HACKER_ID
								FROM 		SUBMISSIONS S2
								WHERE 		S.SUBMISSION_DATE >= S2.SUBMISSION_DATE
								GROUP BY 	HACKER_ID
								HAVING 		S.HACKER_ID = S2.HACKER_ID AND 
											COUNT(DISTINCT SUBMISSION_DATE) = (
												SELECT 	COUNT(DISTINCT SUBMISSION_DATE)
												FROM 	SUBMISSIONS
												WHERE 	SUBMISSION_DATE <= S.SUBMISSION_DATE
											)
							)
				GROUP BY 	SUBMISSION_DATE
			) USING (SUBMISSION_DATE)
ORDER BY 	SUBMISSION_DATE;

SELECT 		SUBMISSION_DATE, CNT, HACKER_ID, NAME
FROM 		(
				SELECT 		SUBMISSION_DATE, HACKER_ID, NAME
				FROM 		HACKERS JOIN (
								SELECT 	ROW_NUMBER() OVER(PARTITION BY SUBMISSION_DATE ORDER BY SUBMISSION_DATE, CNT_S DESC, HACKER_ID) AS RN, T.*
								FROM 	(
									SELECT 		SUBMISSION_DATE, HACKER_ID, COUNT(*) AS CNT_S
									FROM 		SUBMISSIONS
									GROUP BY 	SUBMISSION_DATE, HACKER_ID
									-- HAVING 	COUNT(*) > 0
								) T
								WHERE CNT_S > 0
							) USING(HACKER_ID)
				WHERE 		RN = 1
			) JOIN (
				SELECT 		SUBMISSION_DATE, COUNT(DISTINCT HACKER_ID) AS CNT
				FROM 		SUBMISSIONS S
				WHERE 		(
								SELECT 	COUNT(DISTINCT SUBMISSION_DATE)
								FROM 	SUBMISSIONS S2
								WHERE 	S2.SUBMISSION_DATE <= S.SUBMISSION_DATE AND S2.HACKER_ID = S.HACKER_ID			-- S2.SUBMISSION_DATE < S.SUBMISSION_DATE
							) = (TO_DATE(S.SUBMISSION_DATE, 'YYYY-MM-DD') - TO_DATE('2016-03-01', 'YYYY-MM-DD') + 1)	-- S.SUBMISSION_DATE - TO_DATE('2016-03-01', 'YYYY-MM-DD')
				GROUP BY 	SUBMISSION_DATE
			) USING (SUBMISSION_DATE)
ORDER BY 	SUBMISSION_DATE;