SELECT Contests.contest_id,
    Contests.hacker_id,
    Contests.NAME,
    COALESCE(SUM(Submission_Stats.total_submissions), 0),
    COALESCE(SUM(Submission_Stats.total_accepted_submissions), 0),
    COALESCE(SUM(View_Stats.total_views), 0),
    COALESCE(SUM(View_Stats.total_unique_views), 0)
FROM Contests,
    Colleges,
    Challenges
    LEFT JOIN (
        SELECT challenge_id,
            SUM(total_views) AS total_views,
            SUM(total_unique_views) AS total_unique_views
        FROM View_Stats
        GROUP BY challenge_id
    ) View_Stats ON Challenges.challenge_id = View_Stats.challenge_id
    LEFT JOIN (
        SELECT challenge_id,
            SUM(total_submissions) AS total_submissions,
            SUM(total_accepted_submissions) AS total_accepted_submissions
        FROM Submission_Stats
        GROUP BY challenge_id
    ) Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id
WHERE Contests.contest_id = Colleges.contest_id
    AND Colleges.college_id = Challenges.college_id
GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME
ORDER BY Contests.contest_id;

SELECT Contests.contest_id,
    Contests.hacker_id,
    Contests.NAME,
    IFNULL(SUM(Submission_Stats.total_submissions), 0),
    IFNULL(SUM(Submission_Stats.total_accepted_submissions), 0),
    IFNULL(SUM(View_Stats.total_views), 0),
    IFNULL(SUM(View_Stats.total_unique_views), 0)
FROM Contests,
    Colleges,
    Challenges
    LEFT JOIN (
        SELECT challenge_id,
            SUM(total_views) AS total_views,
            SUM(total_unique_views) AS total_unique_views
        FROM View_Stats
        GROUP BY challenge_id
    ) AS View_Stats ON Challenges.challenge_id = View_Stats.challenge_id
    LEFT JOIN (
        SELECT challenge_id,
            SUM(total_submissions) AS total_submissions,
            SUM(total_accepted_submissions) AS total_accepted_submissions
        FROM Submission_Stats
        GROUP BY challenge_id
    ) AS Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id
WHERE Contests.contest_id = Colleges.contest_id
    AND Colleges.college_id = Challenges.college_id
GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME
ORDER BY Contests.contest_id