[MySQL/Hackerrank] Interviews

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 .

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

 

Input Format

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. 

Sample Input

Contests Table: 

 Colleges Table: 

 Challenges Table: 

 View_Stats Table: 

 Submission_Stats Table: 

Sample Output

66406 17973 Rose 111 39 156 56

66556 79153 Angela 0 0 11 10

94828 80275 Frank 150 38 41 15

 

이번 문제처럼 테이블 관계를 파악하여 푸는 문제는 그래도 쉬운것 같다.

처음에는 데이터는 확인하지 않고 테이블 관계만 체크해서 막 조인을 했다.

그런데 결과가 틀렸다고 나와서 샘플 데이터로 시뮬레이션 하느냐고 1시간 정도 시간이 소요되었다.

(쿼리가 느리다고 결과가 안나와서 기다리는데 꽤 오랜시간 소요..ㅠ)

SELECT A.contest_id
    , A.hacker_id    
    , A.name    
    , SUM(IFNULL(D.total_submissions,0)) AS total_submissions
    , SUM(IFNULL(D.total_accepted_submissions,0)) AS total_accepted_submissions
    , SUM(IFNULL(E.total_views,0)) AS total_views
    , SUM(IFNULL(E.total_unique_views,0)) AS total_unique_views
FROM Contests A
    JOIN Colleges B
    ON A.contest_id = B.contest_id
    JOIN Challenges C
    ON B.college_id = C.college_id      
    LEFT OUTER JOIN (
        SELECT challenge_id    
            , SUM(total_submissions) AS total_submissions
            , SUM(total_accepted_submissions) AS total_accepted_submissions
        FROM Submission_Stats 
        GROUP BY challenge_id
    ) D
    ON C.challenge_id = D.challenge_id     
    LEFT OUTER JOIN (
        SELECT challenge_id
            , SUM(total_views) AS total_views
            , SUM(total_unique_views) AS total_unique_views
        FROM View_Stats 
        GROUP BY challenge_id
    ) E
    ON C.challenge_id = E.challenge_id     
GROUP BY A.contest_id, A.hacker_id, A.name  
HAVING SUM(IFNULL(D.total_submissions,0))
    +SUM(IFNULL(D.total_accepted_submissions,0))
    +SUM(IFNULL(E.total_views,0))
    +SUM(IFNULL(E.total_unique_views,0)) > 0
ORDER BY A.contest_id

 

샘플 데이터 체크는 익숙한 MSSQL로 하였다.

CREATE TABLE #Contests (
	contest_id int,
	hacker_id int,
	name varchar(10)
)
	
CREATE TABLE #Colleges (
	college_id int,
	contest_id int	
)
	
CREATE TABLE #Challenges (
	challenge_id int,
	college_id int	
)
		
	
CREATE TABLE #View_Stats (
	challenge_id int,
	total_views int,
	total_unique_views int
)
		
	
CREATE TABLE #Submission_Stats (
	challenge_id int,
	total_submissions int,
	total_accepted_submissions int
)


insert into #Contests
values (66406,	17973,	'Rose')
	, (66556,	79153,	'Angela')
	, (94828,	80275,	'Frank')

insert into #Colleges
values (11219,	66406)
	,(32473,	66556)
	,(56685,	94828)


insert into #Challenges
values (18765,	11219)
	,(47127,	11219)
	,(60292,	32473)
	,(72974,	56685)

insert into #Submission_Stats	
values (75516,	34,	12)
	,(47127,	27,	10)
	,(47127,	56,	18)
	,(75516,	74,	12)	
	,(75516,	83,	8)	
	,(72974,	68,	24)	
	,(72974,	82,	14)	
	,(47127,	28,	11)	

insert into #View_Stats
values (47127,	26,	19)	
	,(47127,	15,	14)	
	,(18765,	43,	10)
	,(18765,	72,	13)	
	,(75516,	35,	17)	
	,(60292,	11,	10)	
	,(72974,	41,	15)	
	,(75516,	75,	11)	



SELECT A.contest_id
    , A.hacker_id    
    , A.name    
    , SUM(D.total_submissions) [total_submissions]
    , SUM(D.total_accepted_submissions) [total_accepted_submissions]
    , SUM(E.total_views) [total_views]
    , SUM(E.total_unique_views) [total_unique_views]
FROM #Contests A
    JOIN #Colleges B
    ON A.contest_id = B.contest_id
    JOIN #Challenges C
    ON B.college_id	= C.college_id	    
    JOIN #Submission_Stats D
    ON C.challenge_id = D.challenge_id    
    JOIN #View_Stats E
    ON C.challenge_id = E.challenge_id    
GROUP BY A.contest_id, A.hacker_id, A.name  
HAVING SUM(D.total_submissions)+SUM(D.total_accepted_submissions)+SUM(E.total_views)+SUM(E.total_unique_views) > 0
ORDER BY A.contest_id


SELECT A.contest_id
    , A.hacker_id    
    , A.name    
	, SUM(ISNULL(D.total_submissions,0)) [total_submissions]
	, SUM(ISNULL(D.total_accepted_submissions,0)) [total_accepted_submissions]
    , SUM(ISNULL(E.total_views,0)) [total_views]
    , SUM(ISNULL(E.total_unique_views,0)) [total_unique_views]
FROM #Contests A
    JOIN #Colleges B
    ON A.contest_id = B.contest_id
	JOIN #Challenges C
    ON B.college_id	= C.college_id	  
	LEFT OUTER JOIN (
		SELECT challenge_id	
			, SUM(total_submissions) [total_submissions]
			, SUM(total_accepted_submissions) [total_accepted_submissions]
		FROM #Submission_Stats 
		GROUP BY challenge_id
	) D
    ON C.challenge_id = D.challenge_id     
	LEFT OUTER JOIN (
		SELECT challenge_id
			, SUM(total_views) [total_views]
			, SUM(total_unique_views) [total_unique_views]
		FROM #View_Stats 
		GROUP BY challenge_id
	) E
    ON C.challenge_id = E.challenge_id     
GROUP BY A.contest_id, A.hacker_id, A.name  
HAVING SUM(ISNULL(D.total_submissions,0))+SUM(ISNULL(D.total_accepted_submissions,0))+SUM(ISNULL(E.total_views,0))+SUM(ISNULL(E.total_unique_views,0)) > 0
ORDER BY A.contest_id