MICROSOFT SQL SERVER

with s_b as (select c.contest_id, sum(isnull (s.total_submissions, 0)) as to_sub, sum(isnull (s.total_accepted_submissions, 0)) as to_ac_sub from Contests c join Colleges cg on c.contest_id = cg.contest_id join Challenges cl on cg.college_id = cl.college_id left join Submission_Stats s on cl.challenge_id = s.challenge_id group by c.contest_id),

_view as (select c.contest_id, sum(isnull (v.total_views, 0)) as to_view, sum(isnull (v.total_unique_views, 0)) as to_uq_view from Contests c join Colleges cg on c.contest_id = cg.contest_id join Challenges cl on cg.college_id = cl.college_id left join View_Stats v on cl.challenge_id = v.challenge_id group by c.contest_id)

select c.contest_id, c.hacker_id, c.name, s.to_sub, s.to_ac_sub, v.to_view, v.to_uq_view from Contests c join s_b s on c.contest_id = s.contest_id join _view v on c.contest_id = v.contest_id order by c.contest_id;