The question table and table uploaded for Output is mismatched .

SELECT Contests.contest_id,hacker_id,name, COALESCE(SUM(Submission_Stats.total_submissions),0)AS total_submissions, COALESCE(SUM(Submission_Stats.total_accepted_submissions),0)AS total_accepted_submissions, COALESCE(SUM(View_Stats.total_views),0)AS total_views, COALESCE(SUM(View_Stats.total_unique_views),0)AS total_unique_views FROM Contests JOIN Colleges ON Contests.contest_id=Colleges.contest_id JOIN Challenges ON Colleges.college_id=Challenges.college_id JOIN View_Stats ON Challenges.challenge_id=View_Stats.challenge_id JOIN Submission_Stats on Challenges.challenge_id=Submission_Stats.challenge_id GROUP BY Contests.contest_id,Contests.hacker_id,Contests.name HAVING COALESCE(SUM(total_submissions),0)+COALESCE(SUM(total_accepted_submissions),0)+ COALESCE(SUM(total_views),0)+COALESCE(SUM(total_unique_views),0)>0 ORDER BY Contests.contest_id

WITH c1 AS ( SELECT con.contest_id, SUM(NVL(total_submissions, 0)) AS total_sub_sum, SUM(NVL(total_accepted_submissions, 0)) AS tot_acc_sub_sum FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges chl ON col.college_id = chl.college_id LEFT JOIN Submission_Stats subs ON chl.challenge_id = subs.challenge_id GROUP BY con.contest_id ), c2 AS ( SELECT con.contest_id, SUM(NVL(total_views, 0)) AS tot_views_sum, SUM(NVL(total_unique_views, 0)) AS tot_uniq_views_sum FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges chl ON col.college_id = chl.college_id LEFT JOIN View_Stats vws ON chl.challenge_id = vws.challenge_id GROUP BY con.contest_id ) SELECT cts.contest_id,hacker_id,name,c1.total_sub_sum,c1.tot_acc_sub_sum,c2.tot_views_sum,c2.tot_uniq_views_sum FROM Contests cts JOIN c1 ON cts.contest_id = c1.contest_id JOIN c2 ON cts.contest_id = c2.contest_id order by cts.contest_id;

MySQL

SELECT bb.contest_id, bb.hacker_id, bb.name, SUM(cc.ttsm), SUM(cc.ttasm), SUM(dd.ttv), SUM(dd.ttuv) FROM (SELECT ch.challenge_id, aa.contest_id, aa.hacker_id, aa.name FROM (SELECT cl.college_id, ct.contest_id, ct.hacker_id, ct.name FROM contests ct JOIN colleges cl USING (contest_id)) aa JOIN challenges ch USING (college_id)) bb LEFT JOIN (SELECT ch.challenge_id, sum(total_submissions) AS ttsm, sum(total_accepted_submissions) AS ttasm FROM challenges ch JOIN submission_stats ss ON ch.challenge_id = ss.challenge_id GROUP BY ch.challenge_id) cc ON bb.challenge_id = cc.challenge_id LEFT JOIN (SELECT ch.challenge_id, sum(total_views) AS ttv, sum(total_unique_views) AS ttuv FROM challenges ch JOIN view_stats vs ON ch.challenge_id = vs.challenge_id GROUP BY ch.challenge_id) dd ON bb.challenge_id = dd.challenge_id GROUP BY bb.contest_id, bb.hacker_id, bb.name

with cte as (

select c.contest_id,
        sum(total_submissions) as SOTS,
        sum(total_accepted_submissions) as SOTAS
from colleges c 
join challenges as cc  on cc.college_id = c.college_id 
join submission_stats as s on s.challenge_id = cc.challenge_id 

group by c.contest_id

),

cte2 as (

select  c.contest_id,
        sum(total_views) as STV,
        sum(total_unique_views) as STUV
from colleges c
join challenges as cc on c.college_id = cc.college_id 
join view_stats as v on v.challenge_id = cc.challenge_id 
group by c.contest_id 

),

cte3 as (

select c.contest_id , hacker_id , name , SOTS , SoTAS, STV,STUV from contests c join cte as cc on cc.contest_id = c.contest_id join cte2 as ccc on ccc.contest_id = c.contest_id
)

select * from cte3 where sots !=0 and sotas!=0 and stv!=0 and stuv!=0 order by contest_id

Thank you.