WITH cte1 AS (
    SELECT 
        ch.college_id,
        co.contest_id,
        ch.challenge_id
    FROM Colleges co
    
    JOIN Challenges ch 
    ON co.college_id = ch.college_id
),
cte2 AS (
SELECT ct.contest_id
, SUM(COALESCE(ve.total_views, 0)) AS total_views
, SUM(COALESCE(ve.total_unique_views, 0)) AS total_unique_views
FROM cte1 ct
    JOIN View_Stats ve 
    ON ct.challenge_id = ve.challenge_id
GROUP BY ct.contest_id

),
cte3 As
(
SELECT ct.contest_id
, SUM(COALESCE(su.total_submissions, 0)) AS total_submissions
, SUM(COALESCE(su.total_accepted_submissions, 0)) AS total_accepted_submissions
FROM cte1 ct   
  JOIN Submission_Stats su 
  ON ct.challenge_id = su.challenge_id
GROUP BY ct.contest_id
)
SELECT 
    cn.contest_id,
    cn.hacker_id,
    cn.name,
    SUM(total_submissions) As total_submissions,
    SUM(total_accepted_submissions) As total_accepted_submissions,
    SUM(total_views) As total_views,
    SUM(total_unique_views) As total_unique_views
FROM Contests cn

JOIN cte2 ct2 
ON cn.contest_id = ct2.contest_id

JOIN cte3 ct3
ON cn.contest_id = ct3.contest_id
GROUP BY cn.contest_id, cn.hacker_id, cn.name
HAVING SUM(total_submissions) + SUM(total_accepted_submissions) + SUM(total_views) + SUM(total_unique_views) > 0
ORDER BY cn.contest_id;