Q1_h

select 
    con.contest_id, 
    con.hacker_id, 
    con.name,
    sum(total_submissions) total_submissions,
    sum(total_accepted_submissions) total_accepted_submissions,
    sum(total_views) total_views,
    sum(total_unique_views) total_unique_views
from contests con
    left join colleges col on con.contest_id=col.contest_id
    left join challenges chal on col.college_id= chal.college_id
    /* make a table of challenges with total_views and unique views for each challenge and do a         left join on contest table */
    left join (select
                    challenge_id, 
                    sum(total_views) total_views,
                    sum(total_unique_views) total_unique_views
                from view_stats
                group by challenge_id) as v
        on chal.challenge_id = v.challenge_id
    /* make a table of challenges with total_submissions and accepted sumbmissions for each             challenge and do a left join on contest table */
    left join (select
                    challenge_id, 
                    sum(total_submissions) total_submissions,
                    sum(total_accepted_submissions) total_accepted_submissions
                from submission_stats
                group by challenge_id) as subs
        on chal.challenge_id = subs.challenge_id
group by con.contest_id, con.hacker_id, con.name
having (total_views + total_unique_views + total_submissions + total_accepted_submissions) > 0
order by con.contest_id asc;