HackerRank Interviews problem solution

In this HackerRank Interviews problem solution, 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.

Problem solution MS SQL.

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/
;with cte as
(
    select challenge_id, sum(total_views) as sum_views, sum(total_unique_views) as sum_uq_views
    from view_stats
    group by challenge_id
)
,cte2 as
(
    select challenge_id, sum(total_submissions) as sum_submissions, sum(total_accepted_submissions) as sum_acc_submissions
    from submission_stats
    group by challenge_id   
)
,cte4 as
(
select cont.contest_id, cont.hacker_id, cont.name, sum(isnull(c2.sum_submissions,0)) sum_submissions, 
sum(isnull(c2.sum_acc_submissions,0)) as sum_accepted_submissions, sum(isnull(c.sum_views,0)) as sum_views, sum(isnull(c.sum_uq_views,0)) as sum_unique_views   
from contests cont
inner join colleges coll
on cont.contest_id = coll.contest_id
left join challenges cha
on coll.college_id = cha.college_id
left join cte2 c2 
on c2.challenge_id = cha.challenge_id
left join cte c 
on c.challenge_id = cha.challenge_id
group by cont.contest_id, cont.hacker_id, cont.name
)
select contest_id, hacker_id, name, sum_submissions, sum_accepted_submissions, sum_views,  sum_unique_views from cte4
where not (sum_submissions=0 and sum_accepted_submissions=0 and sum_views=0 and sum_unique_views=0)
order by contest_id

Problem solution in Oracle.

select t1.contest_id, t1.hacker_id, t1.name, ss, sas, sv, suv from
(
select C.contest_id, C.hacker_id, C.name, sum(total_views) sv, sum(total_unique_views) suv
from Contests C
inner join Colleges CL on C.contest_id=CL.contest_id
inner join Challenges CH on CH.college_id=CL.college_id
inner join View_Stats V on V.challenge_id=CH.challenge_id
group by C.contest_id, C.hacker_id, C.name
) t1 
inner join (
select C.contest_id, C.hacker_id, C.name, sum(total_submissions) ss, sum(total_accepted_submissions) sas
from Contests C
inner join Colleges CL on C.contest_id=CL.contest_id
inner join Challenges CH on CH.college_id=CL.college_id
inner join Submission_Stats S on S.challenge_id=CH.challenge_id
group by C.contest_id, C.hacker_id, C.name
) t2 on t1.contest_id=t2.contest_id      
order by t1.contest_id
;

Problem solution in DB2.

/*
    Enter your query here and follow these instructions:
    1. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
    2. The AS keyword causes errors, so follow this convention: "Select t.Field From table1 t" instead of "select t.Field From table1 AS t"
    3. Type your code immediately after comment. Don't leave any blank line.
*/
SELECT ct.contest_id, ct.hacker_id, ct.name,
       SUM(a.tot_sub), SUM(a.tot_acc_sub), SUM(b.tot_views), SUM(b.tot_uni_views)
FROM contests ct
JOIN colleges co ON co.contest_id = ct.contest_id
JOIN challenges ch ON ch.college_id = co.college_id
LEFT JOIN
    (SELECT ss.challenge_id challenge_id, 
     SUM(ss.total_submissions) tot_sub, SUM(ss.total_accepted_submissions) tot_acc_sub
    FROM submission_stats ss
    GROUP BY ss.challenge_id) a ON a.challenge_id = ch.challenge_id
LEFT JOIN 
    (SELECT vs.challenge_id challenge_id, 
     SUM(vs.total_views) tot_views, SUM(vs.total_unique_views) tot_uni_views
    FROM view_stats vs
    GROUP BY vs.challenge_id) b ON b.challenge_id = ch.challenge_id
GROUP BY ct.contest_id, ct.Hacker_id, ct.name
HAVING (SUM(a.tot_sub) + SUM(a.tot_acc_sub) + SUM(b.tot_views) + SUM(b.tot_uni_views) ) != 0
ORDER BY ct.contest_id;