HackerRank - Interviews

 

Interviews | HackerRank

 

문제

 

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

 

 

풀이

처음엔 contest_id에 null 값들이 있으면 challenge_id로 채워야 하는 것인가 생각했는데, 단순히 challenge_id 별로 total_submissions, total_accepted_submissions, total_views, total_unique_views 등을 sum 해준다음에 group by, order by, having 의 조건을 걸어주면 되는 거였다...

 

/*
Enter your query here.
*/
select 
    t.contest_id, t.hacker_id, t.name
    , sum(total_submissions) as total_submissions
    , sum(total_accepted_submissions) as total_accepted_submissions
    , sum(sum_total_views) as sum_total_views
    , sum(sum_total_unique_views) as total_unique_views
from
(
select c.contest_id, c.hacker_id, c.name
    , vs.sum_total_views, vs.sum_total_unique_views
    , ss.total_submissions, ss.total_accepted_submissions
from contests c
left join colleges u
on c.contest_id = u.contest_id
left join challenges ch
on u.college_id = ch.college_id
left join (
    select challenge_id, sum(total_views) as sum_total_views, sum(total_unique_views) as sum_total_unique_views
    from view_stats
    group by 1) vs
on ch.challenge_id = vs.challenge_id
left join (
    select challenge_id, sum(total_submissions) as total_submissions, sum(total_accepted_submissions) as total_accepted_submissions
    from submission_stats
    group by 1) ss
on ch.challenge_id = ss.challenge_id 
) t
group by 1, 2, 3
having sum(total_submissions) + sum(total_accepted_submissions) + sum(sum_total_views) + sum(sum_total_unique_views) > 0
order by t.contest_id