Hard: Question 1

SELECT t1.contest_id,
       t1.hacker_id,
       t1.name,
       ss,
       sas,
       sv,
       suv
FROM
  (SELECT C.contest_id,
          C.hacker_id,
          C.name,
          sum(total_views) sv,
          sum(total_unique_views) suv
   FROM Contests C
   INNER JOIN Colleges CL ON C.contest_id=CL.contest_id
   INNER JOIN Challenges CH ON CH.college_id=CL.college_id
   INNER JOIN View_Stats V ON V.challenge_id=CH.challenge_id
   GROUP BY C.contest_id,
            C.hacker_id,
            C.name) t1
INNER JOIN
  (SELECT C.contest_id,
          C.hacker_id,
          C.name,
          sum(total_submissions) ss,
          sum(total_accepted_submissions) sas
   FROM Contests C
   INNER JOIN Colleges CL ON C.contest_id=CL.contest_id
   INNER JOIN Challenges CH ON CH.college_id=CL.college_id
   INNER JOIN Submission_Stats S ON S.challenge_id=CH.challenge_id
   GROUP BY C.contest_id,
            C.hacker_id,
            C.name) t2 ON t1.contest_id=t2.contest_id
ORDER BY t1.contest_id;