https://www.hackerrank.com/challenges/interviews/problem

📔문제

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.

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.
  

---

📝예시

Contests Table:

Colleges Table:

Challenges Table:

View_Stats Table:

Submission_Stats Table:

66406 17973 Rose 111 39 156 56
66556 79153 Angela 0 0 11 10
94828 80275 Frank 150 38 41 15

---

🧮분야

• JOIN

---

📃SQL 코드

select a.contest_id, hacker_id, name,sum(s.ts),sum(s.ta),sum(v.tv),sum(v.tu)
from contests a, colleges b, challenges c,
    (select challenge_id,  sum(total_views) tv, sum(total_unique_views) tu
     from view_stats
     group by challenge_id) v,
    (select challenge_id, sum(total_submissions) ts , sum(total_accepted_submissions) ta
     from submission_stats
     group by challenge_id) s
where a.contest_id=b.contest_id
and b.college_id=c.college_id
and c.challenge_id=v.challenge_id(+)
and c.challenge_id=s.challenge_id(+)--JOIN
group by a.contest_id, hacker_id, name
having sum(s.ts)>0 or sum(s.ta)>0 or sum(v.tv)>0 or sum(v.tu)>0
order by a.contest_id;

---

📰출력 결과

---

📂고찰

https://yurimyurim.tistory.com/13

View_Stats에만 데이터가 있거나 Submission_sTats에만 데이터가 있을 경우를 위해 left Outer JOIN을 해야 했다.

나머지는 challenge_id 별 합계들을 구했고, 그 합계들의 합계를 contest_id 별로 계산했다.