SELECT c.contest_id, c.hacker_id, c.name, COALESCE(SUM(COALESCE(ss.total_submissions,0)),0) AS total_submissions, COALESCE(SUM(COALESCE(ss.total_accepted_submissions,0)),0) AS total_accepted_submissions, COALESCE(SUM(COALESCE(vs.total_views,0)),0) AS total_views, COALESCE(SUM(COALESCE(vs.total_unique_views,0)),0) AS total_unique_views FROM Contests c JOIN ( SELECT DISTINCT college_id, contest_id FROM Colleges ) uc ON c.contest_id = uc.contest_id JOIN ( SELECT DISTINCT challenge_id, college_id FROM Challenges ) ch ON uc.college_id = ch.college_id LEFT JOIN ( SELECT challenge_id, SUM(total_submissions) AS total_submissions, SUM(total_accepted_submissions) AS total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id ) ss ON ch.challenge_id = ss.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(total_views) AS total_views, SUM(total_unique_views) AS total_unique_views FROM View_Stats GROUP BY challenge_id ) vs ON ch.challenge_id = vs.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING ( SUM(COALESCE(ss.total_submissions,0)) > 0 OR SUM(COALESCE(ss.total_accepted_submissions,0)) > 0 OR SUM(COALESCE(vs.total_views,0)) > 0 OR SUM(COALESCE(vs.total_unique_views,0)) > 0 ) ORDER BY c.contest_id;

this is my version:

with cte as 
(
    SELECT a.contest_id, a.hacker_id, a.name,
        sum(e.total_submissions) as s1, 
        sum(e.total_accepted_submissions) as s2, 
        sum(d.total_views) as s3,
        sum(d.total_unique_views) as s4
    FROM 
    Contests a
    left join  Colleges b
    on a.contest_id = b.contest_id
    left join Challenges c
    on b.college_id = c.college_id
    left join 
    (
        select challenge_id, sum(total_views) as total_views,
                         sum(total_unique_views) as total_unique_views
        from View_Stats group by challenge_id
    ) d
    on c.challenge_id = d.challenge_id
    left join 
    (
        select challenge_id, sum(total_submissions) as total_submissions, 
                        sum(total_accepted_submissions) as total_accepted_submissions
        from Submission_Stats group by challenge_id
    ) e 
    on c.challenge_id = e.challenge_id
    group by a.contest_id, a.hacker_id, a.name
    
)
select * from cte 
where s1+s2+s3+s4!=0
order by contest_id

SET NOCOUNT ON;

WITH SUM_TOTAL_SUBMISSIONS AS ( select a.contest_id, a.hacker_id, a.name, sum(e.total_submissions) AS total_submissions, sum(e.total_accepted_submissions) AS total_accepted_submissions From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN SUBMISSION_STATS E ON c.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,SUM_TOTAL_VIEW as( select a.contest_id, a.hacker_id, sum(d.total_views) AS total_views, sum(d.total_unique_views) AS total_unique_views From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN VIEW_STATS D ON C.CHALLENGE_ID = D.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,DATA_FINAL AS( SELECT A.*, B.total_views, B.total_unique_views FROM SUM_TOTAL_SUBMISSIONS A INNER JOIN SUM_TOTAL_VIEW B ON A.contest_id = B.contest_id AND A.hacker_id = B.hacker_id )

SELECt * FROM DATA_FINAL WHERE total_submissions > 0 OR total_unique_views > 0 OR total_accepted_submissions > 0 OR total_views > 0 ORDER BY contest_id

go

go

SELECT c.contest_id, c.hacker_id, c.name, IFNULL(s.ts, 0) AS total_submissions, IFNULL(s.tas, 0) AS total_accepted_submissions, IFNULL(v.tv, 0) AS total_views, IFNULL(v.tuv, 0) AS total_unique_views FROM Contests c LEFT JOIN ( SELECT c.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN Submission_Stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id ) s ON s.contest_id = c.contest_id LEFT JOIN ( SELECT c.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN View_Stats vs ON vs.challenge_id = ch.challenge_id GROUP BY c.contest_id ) v ON v.contest_id = c.contest_id WHERE (IFNULL(s.ts, 0) + IFNULL(s.tas, 0) + IFNULL(v.tv, 0) + IFNULL(v.tuv, 0)) > 0 ORDER BY c.contest_id;