I really didn’t like this question. I wasted so much time trying to validate my query, thinking it was wrong, only to find out it was fine. The real issue was the duplicates in the stats tables (view_stats and submission_stats). It doesn’t make sense why there are duplicates. They should’ve mentioned that in the question. This is a SQL test, not an English test!

my answer - MySQL:

SELECT c.contest_id, c.hacker_id, c.name, sum(coalesce(total_submissions,0)) as total_submissions, sum(coalesce(total_accepted_submissions,0)) as total_accepted_submissions, sum(coalesce(total_views,0)) as total_views, sum(coalesce(total_unique_views,0)) as total_unique_views from Contests as c left join Colleges as col on col.contest_id = c.contest_id left join Challenges as chl on chl.college_id = col.college_id left join ( select challenge_id, sum(total_views) as total_views,sum(total_unique_views) as total_unique_views from View_Stats group by challenge_id ) as vs on vs.challenge_id = chl.challenge_id left join ( select challenge_id, sum(total_submissions) as total_submissions , sum(total_accepted_submissions) as total_accepted_submissions from Submission_Stats group by challenge_id
) as ss on ss.challenge_id = chl. challenge_id group by c.contest_id, c.hacker_id, c.name having not (total_submissions =0 and total_accepted_submissions = 0 and total_views =0 and total_unique_views=0 ) order by c.contest_id;