-- For MS SQL at this platform, but it works on MySQL installed at my laptop.
with subm as (
    select challenge_id
        , sum(total_submissions) as total_submissions
        , sum(total_accepted_submissions) as total_accepted_submissions
    from submission_stats 
    group by challenge_id
)
, views as (
    select challenge_id
        , sum(total_views) as total_views
        , sum(total_unique_views) as total_unique_views
    from view_stats 
    group by challenge_id
)

select con.contest_id
        , con.hacker_id
        , con.name
        , sum(total_submissions)
        , sum(total_accepted_submissions)
        , sum(total_views)
        , sum(total_unique_views)
from contests as con 
join colleges as col on con.contest_id = col.contest_id 
join challenges as cha on  col.college_id = cha.college_id 
left join views on cha.challenge_id = views.challenge_id
left join subm on cha.challenge_id = subm.challenge_id

group by con.contest_id, con.hacker_id, con.name
having sum(total_submissions) + 
        sum(total_accepted_submissions) + 
        sum(total_views) + 
        sum(total_unique_views) > 0
order by con.contest_id