SELECT
    CONTESTS.CONTEST_ID,
    CONTESTS.HACKER_ID,
    CONTESTS.NAME,
    SUM(SUBMISSION_STATS.TOTAL_SUBMISSIONS) AS S1,
    SUM(SUBMISSION_STATS.TOTAL_ACCEPTED_SUBMISSIONS) AS S2,
    SUM(VIEW_STATS.TOTAL_VIEWS) AS S3,
    SUM(VIEW_STATS.TOTAL_UNIQUE_VIEWS) AS S4
FROM CONTESTS
LEFT JOIN COLLEGES
ON CONTESTS.CONTEST_ID = COLLEGES.CONTEST_ID
LEFT JOIN CHALLENGES 
ON CHALLENGES.COLLEGE_ID = COLLEGES.COLLEGE_ID
LEFT JOIN SUBMISSION_STATS
ON SUBMISSION_STATS.CHALLENGE_ID = CHALLENGES.CHALLENGE_ID
LEFT JOIN VIEW_STATS
ON VIEW_STATS.CHALLENGE_ID = CHALLENGES.CHALLENGE_ID
GROUP BY 1,2,3
HAVING NOT S1=0 OR NOT S2=0 

with 
cte_sum_vs as (
    select ct.contest_id
    ,ct.hacker_id
    ,ct.name
    ,sum(vs.total_views) total_views
    ,sum(vs.total_unique_views) total_unique_views
    from view_stats vs
    inner join challenges ch on vs.challenge_id = ch.challenge_id
    inner join colleges cl on ch.college_id = cl.college_id
    inner join contests ct on cl.contest_id = ct.contest_id
    group by ct.hacker_id, ct.contest_id, ct.name
),
cte_sum_ss as (
    select ct.contest_id
    ,ct.hacker_id
    ,ct.name
    ,sum(ss.total_submissions) total_submissions
    ,sum(ss.total_accepted_submissions) total_accepted_submissions
    from submission_stats ss
    inner join challenges ch on ss.challenge_id = ch.challenge_id
    inner join colleges cl on ch.college_id = cl.college_id
    inner join contests ct on cl.contest_id = ct.contest_id
    group by ct.hacker_id, ct.contest_id, ct.name
)
select ss.contest_id
,ss.hacker_id
,ss.name
,ss.total_submissions
,ss.total_accepted_submissions
,vs.total_views
,vs.total_unique_views
from cte_sum_ss ss
inner join cte_sum_vs vs on ss.hacker_id = vs.hacker_id
where 1=1
and (ss.total_submissions + ss.total_accepted_submissions + vs.total_views + vs.total_unique_views) > 0
order by ss.contest_id

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

/*
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;