Untitled

SELECT contests.contest_id,contests.hacker_id, contests.name, SUM(subs), SUM(accsubs), SUM(views_sum), SUM(uviews_sum) FROM contests 
JOIN colleges ON contests.contest_id = colleges.contest_id JOIN challenges ON colleges.college_id = challenges.college_id 
LEFT JOIN (SELECT challenge_id, SUM(total_views) AS views_sum, SUM(total_unique_views) AS uviews_sum
FROM view_stats GROUP BY challenge_id) A ON challenges.challenge_id = A.challenge_id 
LEFT JOIN (SELECT challenge_id, SUM(total_submissions) AS subs, SUM(total_accepted_submissions) AS accsubs FROM submission_stats GROUP BY challenge_id) B on challenges.challenge_id = B.challenge_id
GROUP BY contests.contest_id, contests.hacker_id, contests.name ORDER BY contest_id;