Hard 1

SELECT ct.contest_id,ct.hacker_id,ct.name, 
re1,re2,re3,re4 FROM Contests ct,
    (SELECT cl.contest_id as cid, sum(s) as re1, sum(acs) as re2, sum(v) as re3, sum(uv) as re4
    FROM Colleges cl, 
        (SELECT clge_id1,s,acs,v,uv
        FROM 
            (SELECT ch.college_id as clge_id1, sum(tv) as v,sum(tuv) as uv 
             FROM Challenges ch LEFT JOIN
                (SELECT challenge_id as ci1, sum(total_views) as tv,sum(total_unique_views) as tuv 
                FROM View_stats 
                GROUP BY challenge_id ) as VS on ci1 = ch.challenge_id 
                GROUP BY ch.college_id) as TVS 
        LEFT JOIN
            (SELECT ch.college_id as clge_id2, sum(ts) as s, sum(tas) as acs
             FROM Challenges ch LEFT JOIN
                (SELECT challenge_id as ci2, sum(total_submissions) as ts, 
                 sum(total_accepted_submissions) as tas
                 FROM Submission_Stats GROUP BY challenge_id) as SS
             ON ci2 = ch.challenge_id GROUP BY ch.college_id) as TSS
        on clge_id1 = clge_id2) AS SSVS
    WHERE cl.college_id = clge_id1 GROUP BY cid ) ENDT
WHERE ct.contest_id = cid and re1+re2+re3+re4>0
ORDER BY ct.contest_id;