select
    t.contest_id,
    t.hacker_id,
    t.name,
    sum(t.total_views) total_views,
    sum(t.total_unique_views) total_unique_views,
    sum(t.total_submissions) total_submissions,
    sum(t.total_accepted_submissions) total_accepted_submissions
from
    (
        SELECT
            co.contest_id,
            co.hacker_id,
            co.name,
            COALESCE(sv.total_views, 0) total_views,
            COALESCE(sv.total_unique_views, 0) total_unique_views,
            COALESCE(ss.total_submissions, 0) total_submissions,
            COALESCE(ss.total_accepted_submissions, 0) total_accepted_submissions
        FROM
            Contests co
            LEFT JOIN (
                SELECT
                    a.contest_id contest_id,
                    sum(a.total_views) total_views,
                    sum(a.total_unique_views) total_unique_views
                FROM
                    (
                        SELECT
                            c.challenge_id,
                            c.college_id,
                            co.contest_id,
                            vs.total_views,
                            vs.total_unique_views
                        FROM
                            Challenges c
                            INNER JOIN View_Stats vs ON c.challenge_id = vs.challenge_id
                            INNER JOIN Colleges co ON c.college_id = co.college_id
                    ) a
                GROUP BY
                    a.contest_id
            ) sv ON co.contest_id = sv.contest_id
            LEFT JOIN (
                SELECT
                    b.contest_id contest_id,
                    sum(b.total_submissions) total_submissions,
                    sum(b.total_accepted_submissions) total_accepted_submissions
                FROM
                    (
                        SELECT
                            c.challenge_id,
                            c.college_id,
                            co.contest_id,
                            ss.total_submissions,
                            ss.total_accepted_submissions
                        FROM
                            Challenges c
                            INNER JOIN Submission_Stats ss ON c.challenge_id = ss.challenge_id
                            INNER JOIN Colleges co ON c.college_id = co.college_id
                    ) b
                GROUP BY
                    b.contest_id
            ) ss ON co.contest_id = ss.contest_id
    ) t
where
    not total_views = 0
    or not total_unique_views = 0
    or not total_submissions = 0
    or not total_accepted_submissions = 0
group by
    contest_id,
    hacker_id,
    name
order by
    contest_id