I follow the guy below me, and it worked. I break it up to be more readable:

    
SELECT 
    c.contest_id,
    c.hacker_id, 
    c.name, 
    sum(ts),
    sum(tas),
    sum(tv),
    sum(tuv) 
FROM 
    contests c 
INNER JOIN 
    colleges col 
ON 
    c.contest_id = col.contest_id 
INNER JOIN
    challenges cha 
ON
    cha.college_id = col.college_id 
LEFT JOIN
    (select 
        challenge_id, 
        sum(total_views) as tv, 
        sum(total_unique_views) as tuv 
     from 
        view_stats 
     group by 
        1 ) vs 
ON 
    vs.challenge_id = cha.challenge_id 
LEFT JOIN
    (select 
        challenge_id, 
        sum(total_submissions) as ts, 
        sum(total_accepted_submissions) as tas 
     from 
        submission_stats 
     group by 
        1 ) ss 
ON
    ss.challenge_id = cha.challenge_id 
GROUP BY 
    1,2,3 
HAVING 
    sum(tv)+sum(tuv)+sum(ts)+sum(tas) >0 
ORDER BY 
    contest_id

I tried this, but it does not work. I dunno what the s. is going on :/

SELECT
    ccc.contest_id,
    ccc.hacker_id,
    ccc.name,
    SUM(vst.ss),
    SUM(vst.sas),
    SUM(vst.sv),
    SUM(vst.suv)
FROM      
    (SELECT
        ct.contest_id AS contest_id,
        ct.hacker_id AS hacker_id,
        ct.name AS name,
        c.college_id AS c_id
    FROM
        contests ct
    INNER JOIN
        colleges c
    ON
        ct.contest_id = c.contest_id
    RIGHT JOIN
        challenges ch
    ON
        c.college_id = ch.college_id
    GROUP BY
        ct.contest_id,
        ct.hacker_id,
        ct.name,
        c.college_id
    HAVING
        COUNT(ch.challenge_id) > 0) ccc
INNER JOIN
    (SELECT
        ch.college_id AS c_id,
        IFNULL(SUM(chs.ss),0) AS ss,
        IFNULL(SUM(chs.sas),0)AS sas,
        IFNULL(SUM(chs.sv),0) AS sv,
        IFNULL(SUM(chs.suv),0) AS suv
    FROM
        challenges ch
    LEFT JOIN
        (SELECT
            v.challenge_id AS ch_id,
            IFNULL(SUM(total_submissions),0) AS ss,
            IFNULL(SUM(total_accepted_submissions),0) AS sas,
            IFNULL(SUM(total_views),0) AS sv,
            IFNULL(SUM(total_unique_views),0) AS suv
        FROM
            view_stats v
        LEFT JOIN
            submission_stats s
        ON
            v.challenge_id = s.challenge_id
        GROUP BY 
            ch_id) chs
    ON 
        ch.challenge_id = chs.ch_id
    WHERE   
        NOT (ss = 0 AND sas = 0 AND sv = 0 AND suv = 0)
    GROUP BY
        ch.college_id) vst
ON
    ccc.c_id = vst.c_id
GROUP BY
    ccc.contest_id,
    ccc.hacker_id,
    ccc.name
ORDER BY
    ccc.contest_id

select c.contest_id, c.hacker_id, c.name, sum(ts),sum(tas),sum(tv),sum(tuv) from contests c inner join colleges col on c.contest_id = col.contest_id inner join challenges cha on cha.college_id = col.college_id left join (select challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv from view_stats group by 1 ) vs on vs.challenge_id = cha.challenge_id left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by 1 ) ss on ss.challenge_id = cha.challenge_id group by 1,2,3 having sum(tv)+sum(tuv)+sum(ts)+sum(tas) >0 order by contest_id

might have made it overly complicated, but it finally worked ...

WITH -- Aggregate view_stats by challenge

view_agg AS ( SELECT challenge_id, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM view_stats GROUP BY challenge_id ),

-- Aggregate submission_stats by challenge

submission_agg AS ( SELECT challenge_id, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas FROM submission_stats GROUP BY challenge_id ),

-- Join challenges to view/submission stats

challenge_data AS ( SELECT ch.college_id, ch.challenge_id, COALESCE(sa.ts, 0) AS ts, COALESCE(sa.tas, 0) AS tas, COALESCE(va.tv, 0) AS tv, COALESCE(va.tuv, 0) AS tuv FROM challenges ch LEFT JOIN submission_agg sa ON ch.challenge_id = sa.challenge_id LEFT JOIN view_agg va ON ch.challenge_id = va.challenge_id ),

-- Aggregate all stats by college

college_totals AS ( SELECT college_id, SUM(ts) AS cts, SUM(tas) AS ctas, SUM(tv) AS ctv, SUM(tuv) AS ctuv FROM challenge_data GROUP BY college_id ),

-- Join college_totals to contests and colleges

all_data AS ( SELECT con.hacker_id, con.name, col.contest_id, colt.college_id, colt.cts, colt.ctas, colt.ctv, colt.ctuv FROM college_totals colt JOIN colleges col on colt.college_id = col.college_id JOIN contests con on col.contest_id = con.contest_id),

-- Aggregate all stats by contest

contest_totals AS ( SELECT contest_id, hacker_id, name, SUM(cts) AS total_submissions, SUM(ctas) AS total_accepted_submissions, SUM(ctv) AS total_views, SUM(ctuv) AS total_unique_views FROM all_data GROUP BY contest_id, hacker_id, name )

-- Final filter and select

SELECT contest_id, hacker_id, name, total_submissions, total_accepted_submissions, total_views, total_unique_views FROM contest_totals WHERE (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 ORDER BY contest_id;

WITH tb1 AS ( SELECT col.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY col.contest_id ), tb2 AS ( SELECT col.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN View_Stats vs ON ch.challenge_id = vs.challenge_id GROUP BY col.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tb1.ts, tb1.tas, tb2.tv, tb2.tuv FROM Contests con JOIN tb1 ON con.contest_id = tb1.contest_id JOIN tb2 ON con.contest_id = tb2.contest_id WHERE tb1.ts > 0 AND tb1.tas > 0 AND tb2.tv > 0 AND tb2.tuv > 0 ORDER BY con.contest_id;