SQL Advanced Join - 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 .
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

Explanation

The contest is used in the college . In this college , challenges and are asked, so from the view and submission stats:

• Sum of total submissions
• Sum of total accepted submissions
• Sum of total views
• Sum of total unique views

Simillarly, we can find the sums for contests and .

My Answer
SELECT contests.contest_id,
       contests.hacker_id,
       contests.name,
       Sum(ss.submissions)          AS t_sub,
       Sum(ss.accepted_submissions) AS t_accepted_sub,
       Sum(vs.views)                AS t_views,
       Sum(vs.unique_views)         AS t_unique_views
FROM   contests
       LEFT JOIN colleges
              ON contests.contest_id = colleges.contest_id
       LEFT JOIN challenges
              ON colleges.college_id = challenges.college_id
       LEFT JOIN (SELECT challenge_id,
                         Sum(total_views)        AS views,
                         Sum(total_unique_views) AS unique_views
                  FROM   view_stats
                  GROUP  BY challenge_id) AS vs
              ON challenges.challenge_id = vs.challenge_id
       LEFT JOIN (SELECT challenge_id,
                         Sum(total_submissions)          AS submissions,
                         Sum(total_accepted_submissions) AS
                         accepted_submissions
                  FROM   submission_stats
                  GROUP  BY challenge_id) AS ss
              ON challenges.challenge_id = ss.challenge_id
GROUP  BY contests.contest_id,
          contests.hacker_id,
          contests.name
HAVING ( t_sub + t_accepted_sub + t_views + t_unique_views ) > 0
ORDER  BY contests.contest_id;