-- Try this
select 
    *
from( 
select 
    contest_id,
    hacker_id,
    name,
    coalesce(sum(a),0) as a,
    coalesce(sum(b),0) as b,
    coalesce(sum(c),0) as c,
    coalesce(sum(d),0) as d
from Contests inner join Colleges using(contest_id) inner join Challenges using(college_id)
left join (select challenge_id, sum(total_submissions) a, sum(total_accepted_submissions) b from Submission_Stats group by 1) ab using(challenge_id)
left join (select challenge_id, sum(total_views) c,sum(total_unique_views) d from View_Stats group by 1) bc using(challenge_id)
group by 1,2,3
) abc where a+b+c+d > 0 order by contest_id