/*
Enter your query here.
Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error.
*/

WITH full_view_stats AS (
    SELECT ct.contest_id, ct.hacker_id, ct.name,
        vs.total_views, vs.total_unique_views
    FROM View_Stats vs
    JOIN Challenges ch ON vs.challenge_id = ch.challenge_id
    JOIN Colleges cl ON ch.college_id = cl.college_id
    JOIN Contests ct ON ct.contest_id = cl.contest_id
),
full_submission_stats AS (
    SELECT ct.contest_id, ct.hacker_id, ct.name,
        ss.total_submissions, ss.total_accepted_submissions
    FROM Submission_Stats ss
    JOIN Challenges ch ON ss.challenge_id = ch.challenge_id
    JOIN Colleges cl ON ch.college_id = cl.college_id
    JOIN Contests ct ON ct.contest_id = cl.contest_id
),
reduced_view_stats AS (
    SELECT contest_id, hacker_id, name,
        SUM(total_views) as total_views,
        SUM(total_unique_views) AS total_unique_views
    FROM full_view_stats
    GROUP BY hacker_id, contest_id, name
),
reduced_submission_stats AS (
    SELECT contest_id, hacker_id, name,
        SUM(total_submissions) as total_submissions,
        SUM(total_accepted_submissions) AS total_accepted_submissions
    FROM full_submission_stats
    GROUP BY hacker_id, contest_id, name
)
SELECT ss.contest_id, ss.hacker_id, ss.name,
    (total_submissions),
    (total_accepted_submissions),
    (total_views),
    (total_unique_views)
FROM reduced_submission_stats ss
JOIN reduced_view_stats vs ON ss.hacker_id = vs.hacker_id
WHERE NOT total_submissions = 0 OR
NOT total_accepted_submissions = 0 OR
NOT total_views = 0 OR
NOT total_unique_views = 0
ORDER BY ss.contest_id;