select c.contest_id, c.hacker_id, c.name, sum(ss.ts) as a, sum(ss.tas) as b, sum(vs.tv) as c, sum(vs.tuv) as d
from contests as con 

inner join colleges as co
on con.contest_id = co.contest_id
inner join challenges as ch
on ch.college_id = co.college_id

left join (select challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv from view_stats group by challenge_id) as vs
on ch.challenge_id = vs.challenge_id

left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by challenge_id) as ss
on ss.challenge_id = ch.challenge_id

group by 1,2,3
having (a+b+c+d)<>0
order by 1