[HackerRank SQL] 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 0.

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. 

  

---

Answer

select ct.contest_id, ct.hacker_id, ct.name, sum(ss.total_submissions), sum(ss.total_accepted_submissions), sum(vs.total_views), sum(vs.total_unique_views)
from contests as ct
    left join
    colleges as cl
    on ct.contest_id = cl.contest_id
    left join
    challenges as ch
    on cl.college_id = ch.college_id
    left join
    # total_submissions, total_accepted_submissions 합계
    (select challenge_id, sum(total_submissions) as total_submissions,
      sum(total_accepted_submissions) as total_accepted_submissions
      from Submission_Stats
      group by challenge_id) as ss
     on ch.challenge_id = ss.challenge_id
    left join
    # total_views, total_unique_views 합계
    (select challenge_id, sum(total_views) as total_views,
      sum(total_unique_views) as total_unique_views
      from View_Stats
      group by challenge_id) as vs
     on ch.challenge_id = vs.challenge_id
group by ct.contest_id, ct.hacker_id, ct.name
# Exclude the contest from the result if all four sums are 0.
having sum(ss.total_submissions) + sum(ss.total_accepted_submissions) + sum(vs.total_views) + sum(vs.total_unique_views) > 0
order by ct.contest_id

challenge_id에 따라 total_submissions, total_accepted_submissions, total_views, total_unique_views 의 합계를 구하고 다른 테이블과 병합한 다음 contest_id, hacker_id, name에 따라서 그룹화한다.

Result