Interviews(内连接，左外连接，分组、聚合)

有contests(contest_id, hacker_id, name),colleges(college_id, contest_id),challenges(challenge_id, college_id),view_stats(challenge_id, total_views, total_unique_views),submission_stats(challenge_id, total_submissions, total_accepted_submissions)表

要求输出：

1、contest_id, hacker_id, name, total_submissions和，total_accepted_submissions和，total_views和，total_unique_views和

2、contest_id升序

sql:

select con.contest_id, con.hacker_id, con.name, sum(t2.sumTotalSubmissions), sum(t2.sumTotalAcceptedSubmissions), sum(t1.sumTotalViews), sum(t1.sumTotalUniqueViews) from contests con inner join colleges col on con.contest_id = col.contest_id inner join challenges cha on col.college_id = cha.college_id
left join
(select challenge_id, sum(total_views) as sumTotalViews, sum(total_unique_views) as sumTotalUniqueViews from view_stats group by challenge_id) t1 on cha.challenge_id = t1.challenge_id
left join
(select challenge_id, sum(total_submissions) as sumTotalSubmissions, sum(total_accepted_submissions) as sumTotalAcceptedSubmissions from submission_stats group by challenge_id) t2 on cha.challenge_id = t2.challenge_id
 group by con.contest_id, con.hacker_id, con.name having sum(t2.sumTotalSubmissions) != 0 or sum(t2.sumTotalAcceptedSubmissions) != 0 or sum(t1.sumTotalViews) != 0 or sum(t1.sumTotalUniqueViews) != 0 order by con.contest_id;