Question 1 (Interviews)

Hard:

Question 1 (Interviews):


SELECT contest.contest_id, contest.hacker_id, contest.name, sum(total_submissions), sum(total_accepted_submissions), sum(total_views),sum(total_unique_views)

FROM contests Contest 
join colleges College ON contest.contest_id = College.contest_id 
join challenges Challenge ON  College.college_id = Challenge.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) temp1 ON Challenge.challenge_id = temp1.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) temp2 ON Challenge.challenge_id = temp2.challenge_id group by contest.contest_id, contest.hacker_id, contest.name

HAVING sum(total_submissions)!=0 OR sum(total_accepted_submissions)!=0 OR sum(total_views)!=0 OR sum(total_unique_views)!=0 

order by contest_id;