WITH tb1 AS ( SELECT col.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY col.contest_id ), tb2 AS ( SELECT col.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY col.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tb1.ts, tb1.tas, tb2.tv, tb2.tuv FROM Contests con JOIN tb1 ON con.contest_id = tb1.contest_id JOIN tb2 ON con.contest_id = tb2.contest_id WHERE tb1.ts >0 AND tb1.tas>0 AND tb2.tv>0 AND tb2.tuv>0 ORDER BY con.contest_id;

SELECT C.contest_id, C.hacker_id, C.name, COALESCE(SUM(ST.total_submissions), 0) AS total_submissions, COALESCE(SUM(ST.total_accepted_submissions), 0) AS total_accepted_submissions, COALESCE(SUM(VW.total_views), 0) AS total_views, COALESCE(SUM(VW.total_unique_views), 0) AS total_unique_views FROM Contests C JOIN Colleges CL ON CL.contest_id = C.contest_id LEFT JOIN Challenges CH ON CH.college_id = CL.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) VW ON VW.challenge_id = CH.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) ST ON ST.challenge_id = CH.challenge_id GROUP BY C.contest_id, C.hacker_id, C.name HAVING SUM(ST.total_submissions) + SUM(ST.total_accepted_submissions) + SUM(VW.total_views) + SUM(VW.total_unique_views) > 0 ORDER BY C.contest_id;

Select Contest_id,hacker_id, name, sum(total_submissions), sum(total_accepted_submissions), sum (total_views),sum(total_unique_views) From ( SELECT challenges.challenge_id as challenge_id, Colleges.college_id, Contests.Contest_id as Contest_id, total_views,total_unique_views,total_submissions,total_accepted_submissions, hacker_id, name from Contests inner join Colleges on Contests.Contest_id = Colleges.Contest_id inner join Challenges on challenges.college_id = colleges.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 ) tmp1 on tmp1.challenge_id = Challenges.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 ) tmp2 on tmp2.challenge_id = Challenges.challenge_id ) tmp3 Group by Contest_id,hacker_id,name having sum(total_views)+sum(total_unique_views)+sum(total_submissions)+sum(total_accepted_submissions)>0 order by Contest_id

Throws error related to cte. /Getting contest details/ with contest as ( select a.contest_id,a.hacker_id,a.name, b.college_id,b.challenge_id from Contests a left join Colleges b on a.contest_id=b.contest_id left join Challenges c on b.college_id=c.college_id), /Aggregating stats/ stats_details as (select challenge_id,sum(total_views) as tot_views, sum(total_unique_views) as tot_uniq_views from View_Stats ) group by challenge_id ), /Aggregating submission stats/ sub_stats as (select challenge_id,sum(total_submissions) as tot_sub, sum(total_accepted_submissions) as tot_acp_sub from Submission_Stats group by challenge_id) select b.contest_id,b.hacker_id,b.name, a.* from ( select a.college_id, sum(b.tot_views) as total_views, sum(b.tot_uniq_views) as total_unique_views, sum(c.tot_sub) as total_submissions, sum(c.tot_acp_sub) as total_accepted_submissions from Colleges a left join stats_details b on a.challenge_id=b.challenge_id left join sub_stats c on a.challenge_id=c.challenge_id group by a.college_id )a inner join contest b on a.contest_id=b.contest_id where a.total_views+total_unique_views+total_submissions+total_accepted_submissions>0;

SELECT c.contest_id, c.hacker_id, c.name, SUM(ss.total_submissions) AS total_submissions, SUM(ss.total_accepted_submissions) AS total_accepted_submissions, SUM(vs.total_views) AS total_views, SUM(vs.total_unique_views) AS total_unique_views FROM contests c JOIN colleges col ON c.contest_id = col.contest_id JOIN challenges ch ON col.college_id = ch.college_id LEFT JOIN submission_stats ss ON ch.challenge_id = ss.challenge_id LEFT JOIN view_stats vs ON ch.challenge_id = vs.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING SUM(ss.total_submissions) > 0 OR SUM(ss.total_accepted_submissions) > 0 OR SUM(vs.total_views) > 0 OR SUM(vs.total_unique_views) > 0 ORDER BY c.contest_id;