[HackerRank][SQL(Oracle)] Interviews

 

※  The following tables hold interview data:

• Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.
  
• Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.
  
• Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.
  
• View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.
  
• Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.
  

 

■  [Advanced Select]  Interviews

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

Q.

Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are 0.

Note: A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.

A.

SELECT 		CONTEST_ID, HACKER_ID, NAME
			, SUM_TOTAL_SUBMISSIONS
			, SUM_TOTAL_ACCEPTED_SUBMISSIONS
			, SUM_TOTAL_VIEWS
			, SUM_TOTAL_UNIQUE_VIEWS
FROM 		CONTESTS JOIN (
				SELECT 		CONTEST_ID
							, NVL(SUM(SUM_TOTAL_SUBMISSIONS),0) AS SUM_TOTAL_SUBMISSIONS
							, NVL(SUM(SUM_TOTAL_ACCEPTED_SUBMISSIONS),0) AS SUM_TOTAL_ACCEPTED_SUBMISSIONS
							, NVL(SUM(SUM_TOTAL_VIEWS),0) AS SUM_TOTAL_VIEWS
							, NVL(SUM(SUM_TOTAL_UNIQUE_VIEWS),0) AS SUM_TOTAL_UNIQUE_VIEWS
				FROM 		COLLEGES JOIN CHALLENGES USING(COLLEGE_ID) 
							JOIN (
								SELECT 		*
								FROM 		(
												SELECT 		CHALLENGE_ID
															, SUM(TOTAL_VIEWS) AS SUM_TOTAL_VIEWS
															, SUM(TOTAL_UNIQUE_VIEWS) AS SUM_TOTAL_UNIQUE_VIEWS
												FROM 		VIEW_STATS
												GROUP BY 	CHALLENGE_ID
											) FULL JOIN (
												SELECT 		CHALLENGE_ID
															, SUM(TOTAL_SUBMISSIONS) AS SUM_TOTAL_SUBMISSIONS
															, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS SUM_TOTAL_ACCEPTED_SUBMISSIONS
												FROM 		SUBMISSION_STATS
												GROUP BY 	CHALLENGE_ID
											) USING(CHALLENGE_ID)
							) USING(CHALLENGE_ID)
				WHERE 		SUM_TOTAL_SUBMISSIONS IS NOT NULL OR 
							SUM_TOTAL_ACCEPTED_SUBMISSIONS IS NOT NULL OR 
							SUM_TOTAL_VIEWS IS NOT NULL OR 
							SUM_TOTAL_UNIQUE_VIEWS IS NOT NULL
				GROUP BY 	CONTEST_ID
			) USING (CONTEST_ID)
ORDER BY 	CONTEST_ID;

SELECT 		CONTEST_ID, HACKER_ID, NAME
			, NVL(SUM(SUM_TOTAL_SUBMISSIONS),0) 			AS SUM_TOTAL_SUBMISSIONS
			, NVL(SUM(SUM_TOTAL_ACCEPTED_SUBMISSIONS),0)	AS SUM_TOTAL_ACCEPTED_SUBMISSIONS
			, NVL(SUM(SUM_TOTAL_VIEWS),0) 				AS SUM_TOTAL_VIEWS
			, NVL(SUM(SUM_TOTAL_UNIQUE_VIEWS),0) 			AS SUM_TOTAL_UNIQUE_VIEWS
FROM 		CONTESTS JOIN COLLEGES 	USING(CONTEST_ID) 
			JOIN CHALLENGES 			USING(COLLEGE_ID) 
			JOIN (
				SELECT 	*
				FROM 	(
							SELECT 		CHALLENGE_ID
										, SUM(TOTAL_VIEWS) 			AS SUM_TOTAL_VIEWS
										, SUM(TOTAL_UNIQUE_VIEWS) 	AS SUM_TOTAL_UNIQUE_VIEWS
							FROM 		VIEW_STATS
							GROUP BY 	CHALLENGE_ID
						) FULL JOIN (
							SELECT 		CHALLENGE_ID
										, SUM(TOTAL_SUBMISSIONS) 			AS SUM_TOTAL_SUBMISSIONS
										, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS SUM_TOTAL_ACCEPTED_SUBMISSIONS
							FROM 		SUBMISSION_STATS
							GROUP BY 	CHALLENGE_ID
						) USING(CHALLENGE_ID)
				WHERE 	SUM_TOTAL_SUBMISSIONS IS NOT NULL 
						OR SUM_TOTAL_ACCEPTED_SUBMISSIONS IS NOT NULL 
						OR SUM_TOTAL_VIEWS IS NOT NULL 
						OR SUM_TOTAL_UNIQUE_VIEWS IS NOT NULL
			) USING(CHALLENGE_ID)
GROUP BY 	CONTEST_ID, HACKER_ID, NAME
ORDER BY 	CONTEST_ID;