SELECT 
    c.contest_id,
    c.hacker_id,
    c.name,
    COALESCE(SUM(ss.total_submissions), 0) AS total_submit,
    COALESCE(SUM(ss.total_accepted_submissions), 0) AS total_accpt,
    COALESCE(SUM(vs.total_views), 0) AS total_view,
    COALESCE(SUM(vs.total_unique_views), 0) AS total_uni_view
FROM Contests c
JOIN Colleges clg ON c.contest_id = clg.contest_id
JOIN Challenges ch ON clg.college_id = ch.college_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
) ss ON ch.challenge_id = ss.challenge_id
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
) vs ON ch.challenge_id = vs.challenge_id
GROUP BY c.contest_id, c.hacker_id, c.name
HAVING 
    COALESCE(SUM(ss.total_submissions), 0) +
    COALESCE(SUM(ss.total_accepted_submissions), 0) +
    COALESCE(SUM(vs.total_views), 0) +
    COALESCE(SUM(vs.total_unique_views), 0) > 0
ORDER BY c.contest_id;