with CTE_sub as ( select challenge_id,sum(total_submissions) as TS,sum(total_accepted_submissions) as TAS from submission_stats group by challenge_id), CTE_View as( select challenge_id,sum(total_views) as TV,sum(total_unique_views) as TUV from view_stats group by challenge_id)

select a.contest_id,a.hacker_id,a.name,isnull(e.ts,0) as sum_of_Total_submissions ,isnull(e.tas,0) as sum_of_Total_accepted_submissions,isnull(d.tv,0) as sum_of_Total_view ,isnull(d.tuv,0) as sum_of_Total_unique_view from contests a join colleges as b on a.contest_id=b.contest_id join challenges as c on c.college_id=b.college_id left join CTE_view as d on d.challenge_id = c.challenge_id left join CTE_sub as e on e.challenge_id = c.challenge_id where a.contest_id in (66556,94828)

I checked in my local return same result but showing error anybody help me guys!