문제

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.

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

문제 해석

1. contests 마다 contest_id, hacker_id, name을 출력하고 contest에 속하는 view_stats와 submissions_stats의 total_views, total_unique_views, total_submissions, total_accepted_submissions의 각각의 합을 구하라.
2. contest_id로 정렬하라.
3. 단, 각각의 합의 합계가 0이 되는 row는 포함하지 않는다.

문제 분석

• 문제는 단순하다. 어렵게 생각하지 말고 그대로 구하면 된다.
• 테이블이 주로 1:N 관계가 성립하므로 이를 주의하여 코드를 작성해야 한다.
• 하위 테이블로부터 상위로 연속적으로 합계를 구해야만 한다.

정답

select ct.contest_id, ct.hacker_id, ct.name,
sum(ss.ts) as sots, 
sum(ss.tas) as sotas,
sum(vs.tv) as sotv, 
sum(vs.tuv) as sotuv
from contests as ct
left join colleges as cl on cl.contest_id = ct.contest_id
left join challenges as cg on cg.college_id = cl.college_id
left join (
    select 
    challenge_id, 
    sum(total_views) as tv, 
    sum(total_unique_views) as tuv 
    from view_stats group by challenge_id
     ) as vs 
     on vs.challenge_id = cg.challenge_id
left join (
    select 
    challenge_id, 
    sum(total_submissions) as ts, 
    sum(total_accepted_submissions) as tas 
    from submission_stats group by challenge_id
) as ss 
on ss.challenge_id = cg.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
having (sots+sotas+sotv+sotuv) > 0
order by ct.contest_id;

기타

• 계속 통과가 안되다가 일정 시간 후 통과가 됀다.
  • 이유를 모르겠다.
  • 인터넷 및 hackerrank의 서버 문제로 보임