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