Hard - Q1

select contests.contest_id, contests.hacker_id, contests.name, sum(total_submissions),sum(total_accepted_submissions), sum(total_views), sum(total_unique_views) from contests join colleges on contests.contest_id = colleges.contest_id join challenges on challenges.college_id = colleges.college_id left join (select challenge_id, sum(total_submissions) total_submissions, sum(total_accepted_submissions) total_accepted_submissions from submission_stats group by challenge_id) subs on challenges.challenge_id = subs.challenge_id left join (select challenge_id, sum(total_views) total_views, sum(total_unique_views) total_unique_views from view_stats group by challenge_id) totViews on challenges.challenge_id = totViews.challenge_id group by contests.contest_id, contests.hacker_id, contests.name having sum(total_submissions) > 0 and sum(total_accepted_submissions) > 0 and sum(total_views) > 0 and sum(total_unique_views) > 0 order by contest_id;