Stuck on the Hard Rated SQL Hackerrank Problems? Never Fear, Code Walkthrough for the Advanced Join is below.

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.

The solution is below so beware of spoilers:

The key relates to joining through the tables so that all requisite columns are available.

WITH views as (

SELECT challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv

FROM view_stats

GROUP BY challenge_id

)

,

submissions as (

SELECT challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas

from submission_stats

GROUP BY challenge_id

)

,

base as (

SELECT con.contest_id, hacker_id, name, challenge_id

FROM contests as con inner join colleges as col

on con.contest_id = col.contest_id

inner join challenges as chal on chal.college_id = col.college_id

)

SELECT contest_id, hacker_id, name, sum(ts), sum(tas), sum(tv), sum(tuv)

FROM base as b left join submissions as s

on b.challenge_id = s.challenge_id

left join views as v on v.challenge_id = b.challenge_id

GROUP BY

contest_id, hacker_id, name

ORDER BY contest_id asc