SELECT * FROM (
    SELECT 
        C.CONTEST_ID, 
        C.HACKER_ID, 
        C.NAME, 
        COALESCE(SUM(E.TS), 0) AS TS,
        COALESCE(SUM(E.TAS), 0) AS TAS,
        COALESCE(SUM(D.TOTAL_VIEWS), 0) AS TV,
        COALESCE(SUM(D.TUV), 0) AS TUV
        
    FROM CONTESTS AS C
    LEFT JOIN Colleges AS CO ON C.CONTEST_ID = CO.CONTEST_ID
    LEFT JOIN CHALLENGES AS CH ON CO.COLLEGE_ID = CH.COLLEGE_ID
    LEFT JOIN (
        SELECT 
            CHALLENGE_ID, 
            SUM(TOTAL_VIEWS) AS TOTAL_VIEWS, 
            SUM(TOTAL_UNIQUE_VIEWS) AS TUV
        FROM VIEW_STATS
        GROUP BY CHALLENGE_ID
    ) AS D ON CH.CHALLENGE_ID = D.CHALLENGE_ID
    LEFT JOIN (
        SELECT 
            CHALLENGE_ID, 
            SUM(TOTAL_SUBMISSIONS) AS TS, 
            SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TAS
        FROM Submission_Stats 
        GROUP BY CHALLENGE_ID
    ) AS E ON CH.CHALLENGE_ID = E.CHALLENGE_ID
    GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME
) AS CTE
WHERE TV + TS + TUV + TAS != 0
ORDER BY CONTEST_ID;