with college_id as
    (select  co.name, co.contest_id, co.hacker_id, ch.challenge_id from contests co
    join colleges cl on co.contest_id = cl.contest_id
    join challenges ch on cl.college_id = ch.college_id)
select c.contest_id, c.hacker_id, c.name, sum(s.total_submissions), sum(s.total_accepted_submissions), sum(v.total_views), sum(v.total_unique_views) from college_id c
join submission_stats s on c.challenge_id = s.challenge_id
join view_stats v on c.challenge_id = v.challenge_id
where (s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) <> 0
group by c.contest_id, c.hacker_id, c.name
order by c.contest_id;