SELECT c.contest_id, c.hacker_id, c.name, IFNULL(s.ts, 0) AS total_submissions, IFNULL(s.tas, 0) AS total_accepted_submissions, IFNULL(v.tv, 0) AS total_views, IFNULL(v.tuv, 0) AS total_unique_views FROM Contests c LEFT JOIN ( SELECT c.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN Submission_Stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id ) s ON s.contest_id = c.contest_id LEFT JOIN ( SELECT c.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Contests c JOIN Colleges co ON co.contest_id = c.contest_id JOIN Challenges ch ON ch.college_id = co.college_id JOIN View_Stats vs ON vs.challenge_id = ch.challenge_id GROUP BY c.contest_id ) v ON v.contest_id = c.contest_id WHERE (IFNULL(s.ts,0) + IFNULL(s.tas,0) + IFNULL(v.tv,0) + IFNULL(v.tuv,0)) > 0 ORDER BY c.contest_id;

SELECT c.contest_id, c.hacker_id, c.name, COALESCE(SUM(ss.total_submissions), 0) as total_submissions, COALESCE(SUM(ss.total_accepted_submissions), 0) as total_accepted_submissions, COALESCE(SUM(vs.total_views), 0) as total_views, COALESCE(SUM(vs.total_unique_views), 0) as total_unique_views FROM Contests c LEFT JOIN Colleges col ON c.contest_id = col.contest_id LEFT JOIN Challenges ch ON col.college_id = ch.college_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 ) ss ON ch.challenge_id = ss.challenge_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 ) vs ON ch.challenge_id = vs.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING NOT (COALESCE(SUM(ss.total_submissions), 0) = 0 AND COALESCE(SUM(ss.total_accepted_submissions), 0) = 0 AND COALESCE(SUM(vs.total_views), 0) = 0 AND COALESCE(SUM(vs.total_unique_views), 0) = 0) ORDER BY c.contest_id;

SELECT c.contest_id, c.hacker_id, c.name, t.total_submissions, t.total_accepted_submissions, t.total_views, t.total_unique_views FROM Contests AS c JOIN ( SELECT m.contest_id, SUM(IFNULL(ss.ts, 0)) AS total_submissions, SUM(IFNULL(ss.tas, 0)) AS total_accepted_submissions, SUM(IFNULL(vs.tv, 0)) AS total_views, SUM(IFNULL(vs.tuv, 0)) AS total_unique_views FROM ( SELECT ch.challenge_id, col.contest_id FROM Colleges AS col JOIN Challenges AS ch ON ch.college_id = col.college_id ) AS m LEFT JOIN ( SELECT challenge_id, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas FROM Submission_Stats GROUP BY challenge_id ) AS ss ON ss.challenge_id = m.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM View_Stats GROUP BY challenge_id ) AS vs ON vs.challenge_id = m.challenge_id GROUP BY m.contest_id HAVING (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 ) AS t ON t.contest_id = c.contest_id ORDER BY c.contest_id;

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!

WITH cte1 as ( SELECT c.contest_id , c.hacker_id , c.name , SUM(ss.total_submissions) as sum_total_submissions , SUM(ss.total_accepted_submissions) as sum_total_accepted_submissions FROM Contests c JOIN Colleges c2 ON c.contest_id = c2.contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id FULL OUTER JOIN Submission_Stats ss ON c3.challenge_id = ss.challenge_id WHERE c.contest_id is not null GROUP BY c.contest_id , c.hacker_id , c.name ), cte2 as ( SELECT c.contest_id , c.hacker_id , c.name , SUM(vs.total_views) as sum_total_views , SUM(vs.total_unique_views) sum_total_unique_views FROM Contests c JOIN Colleges c2 ON c.contest_id = c2.contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id JOIN View_Stats vs ON c3.challenge_id = vs.challenge_id GROUP BY c.contest_id , c.hacker_id , c.name ) SELECT cte1.contest_id , cte1.hacker_id , cte1.name , ISNULL(sum_total_submissions, 0) , ISNULL(sum_total_accepted_submissions, 0) , ISNULL(sum_total_views, 0) , ISNULL(sum_total_unique_views, 0) FROM cte1 FULL OUTER JOIN cte2 ON cte1.contest_id = cte2.contest_id WHERE sum_total_submissions > 0 OR sum_total_accepted_submissions > 0 OR sum_total_views > 0 OR sum_total_unique_views > 0 ORDER BY cte1.contest_id