MySQL

SELECT bb.contest_id, bb.hacker_id, bb.name, SUM(cc.ttsm), SUM(cc.ttasm), SUM(dd.ttv), SUM(dd.ttuv) FROM (SELECT ch.challenge_id, aa.contest_id, aa.hacker_id, aa.name FROM (SELECT cl.college_id, ct.contest_id, ct.hacker_id, ct.name FROM contests ct JOIN colleges cl USING (contest_id)) aa JOIN challenges ch USING (college_id)) bb LEFT JOIN (SELECT ch.challenge_id, sum(total_submissions) AS ttsm, sum(total_accepted_submissions) AS ttasm FROM challenges ch JOIN submission_stats ss ON ch.challenge_id = ss.challenge_id GROUP BY ch.challenge_id) cc ON bb.challenge_id = cc.challenge_id LEFT JOIN (SELECT ch.challenge_id, sum(total_views) AS ttv, sum(total_unique_views) AS ttuv FROM challenges ch JOIN view_stats vs ON ch.challenge_id = vs.challenge_id GROUP BY ch.challenge_id) dd ON bb.challenge_id = dd.challenge_id GROUP BY bb.contest_id, bb.hacker_id, bb.name