MYSQL Solution

select c.contest_id, c.hacker_id, c.name, sum(ss.ts) as a, sum(ss.tas) as b, sum(vs.tv) as c, sum(vs.tuv) as d
from contests as con 

inner join colleges as co
on con.contest_id = co.contest_id
inner join challenges as ch
on ch.college_id = co.college_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 ch.challenge_id = vs.challenge_id

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 = ch.challenge_id

group by 1,2,3
having (a+b+c+d)<>0
order by 1

-- MySQL Solution

SELECT * FROM ( SELECT c.contest_id, c.hacker_id, c.name,
SUM(A) A, SUM(B) B, SUM(C) C, SUM(D) D FROM Contests c LEFT JOIN Colleges co ON co.contest_id = c.contest_id LEFT JOIN Challenges ch ON ch.college_id = co.college_id LEFT JOIN ( SELECT challenge_id, SUM(v.total_views) C, SUM(v.total_unique_views) D FROM View_Stats v GROUP BY challenge_id) E ON E.challenge_id = ch.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(s.total_submissions) A, SUM(s.total_accepted_submissions) B FROM Submission_Stats s GROUP BY challenge_id) F ON F.challenge_id = ch.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name ORDER BY c.contest_id ) AS G WHERE A != 0 AND B != 0 AND C != 0 AND D != 0;

My sql solution:

select c.contest_id, c.hacker_id, c.name, coalesce(s.total_Submission,0) total_Submission, coalesce(s.total_accepted_Submission,0) total_accepted_Submission, coalesce(v.total_views,0) total_views, coalesce(v.total_unique_views,0) total_unique_views from contests c left join ( select
co.contest_id, sum(ss.total_Submissions) total_Submission, sum(ss.total_accepted_Submissions) total_accepted_Submission from colleges co inner join challenges ch on ch.college_id=co.college_id inner join Submission_Stats ss on ss.challenge_id= ch.challenge_id group by co.contest_id ) s on s.contest_id=c.contest_id left join ( select co.contest_id, sum(vs.total_views) total_views, sum(vs.total_unique_views) total_unique_views from colleges co inner join challenges ch on ch.college_id=co.college_id inner join view_stats vs on vs.challenge_id= ch.challenge_id group by co.contest_id ) v on v.contest_id=c.contest_id where total_Submission+total_accepted_Submission+total_views+ total_unique_views>0 ;

SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSION) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSION) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEW) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEW) AS TOTAL_UNIQUE_VIEWS FROM CONTESTS AS A LEFT JOIN COLLEGES AS B ON A.CONTEST_ID = B.CONTEST_ID LEFT JOIN CHALLENGES AS C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_VIEWS) AS TOTAL_VIEW, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEW FROM VIEW_STATS GROUP BY CHALLENGE_ID) AS D ON C.CHALLENGE_ID = D.CHALLENGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSION, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSION FROM SUBMISSION_STATS GROUP BY CHALLENGE_ID) AS E ON C.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME HAVING (TOTAL_SUBMISSIONS + TOTAL_ACCEPTED_SUBMISSIONS + TOTAL_VIEWS + TOTAL_UNIQUE_VIEWS) > 0 ORDER BY A.CONTEST_ID;