Horrible question.

Logically view_stats rows > submissions_stats rows , as people will view before submit, it makes no sense that some rows in submission_stats is not present in view_stats.

And because of this instead of (contests inner join college inner join challenges ) left join (view_stats left join submission_stats on vs.challenge_id = ss.challenge_id) ,

you'll need to (contests inner join college inner join challenges) left join (view_stats) on contest.challenge_id = vs.challenge_id left join (submission_stats) contest.challenge_id = ss.challenge_id

select * from (select contest_id, hacker_id, name, coalesce(sum(total_submissions),0) as ts, coalesce(sum(total_accepted_submissions),0) as tas, coalesce(sum(total_views),0) as tv, coalesce(sum(total_unique_views),0) as tuv from ((select cts.contest_id, hacker_id, name, chln.challenge_id from contests cts join colleges clg on cts.contest_id = clg.contest_id join challenges chln on clg.college_id = chln.college_id)sqa 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)sqb on sqa.challenge_id = sqb.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)sqc on sqa.challenge_id = sqc.challenge_id) group by contest_id, hacker_id, name)sq1 where ts <> 0 or tas <> 0 or tv <> 0 or tuv <> 0 order by contest_id;