WITH submissions AS (
    SELECT
        co.contest_id,
        SUM(ss.total_submissions) tot_sub,
        SUM(ss.total_accepted_submissions) tot_acc_sub
    FROM 
        Colleges co
            JOIN
        Challenges ch ON co.college_id = ch.college_id 
            JOIN
        Submission_Stats ss ON ch.challenge_id = ss.challenge_id
    GROUP BY co.contest_id
), 
views AS (
    SELECT
        co.contest_id,
        SUM(vs.total_views) tot_views,
        SUM(vs.total_unique_views) tot_uni_views
    FROM 
        Colleges co
            JOIN
        Challenges ch ON co.college_id = ch.college_id 
            JOIN
        View_Stats vs ON ch.challenge_id = vs.challenge_id
    GROUP BY co.contest_id
)

SELECT 
    c.contest_id,
    c.hacker_id,
    c.name,
    s.tot_sub,
    s.tot_acc_sub,
    v.tot_views,
    v.tot_uni_views
FROM
    Contests c
        JOIN
    submissions s ON c.contest_id = s.contest_id
        JOIN
    views v ON s.contest_id = v.contest_id