MySQL Query:

SELECT Contests.contest_id, hacker_id, name, SUM(COALESCE(total_submissions, 0)) AS total_submissions, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM Contests JOIN ( SELECT DISTINCT college_id, contest_id FROM Colleges ) AS colg ON Contests.contest_id = colg.contest_id JOIN ( SELECT DISTINCT college_id, challenge_id FROM Challenges ) AS chlng ON colg.college_id = chlng.college_id LEFT JOIN ( SELECT challenge_id, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM View_Stats GROUP BY challenge_id ) AS vs ON chlng.challenge_id = vs.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(COALESCE(total_submissions, 0)) AS total_submissions, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id ) AS ss ON chlng.challenge_id = ss.challenge_id GROUP BY Contests.contest_id, hacker_id, name HAVING SUM(COALESCE(total_submissions, 0)) != 0 OR SUM(COALESCE(total_accepted_submissions, 0)) != 0 OR SUM(COALESCE(total_views, 0)) != 0 OR SUM(COALESCE(total_unique_views, 0)) != 0 ORDER BY Contests.contest_id;

select Contests.contest_id, hacker_id, name , SUM(COALESCE(total_submissions,0)), SUM(COALESCE(total_accepted_submissions,0)) , SUM(COALESCE(total_views,0)), SUM(COALESCE(total_unique_views,0)) from Contests JOIN (Select distinct college_id, contest_id from Colleges) colg on Contests.contest_id = colg.contest_id JOIN (Select distinct college_id, challenge_id from Challenges) chlng on colg.college_id = chlng.college_id LEFT JOIN (select challenge_id, SUM(COALESCE(total_views,0)) as total_views, SUM(COALESCE(total_unique_views,0)) as total_unique_views from View_Stats group by challenge_id) vs on chlng.challenge_id = vs.challenge_id LEFT JOIN (select challenge_id, SUM(COALESCE(total_submissions,0)) as total_submissions, SUM(COALESCE(total_accepted_submissions,0)) as total_accepted_submissions from Submission_Stats group by challenge_id) ss on chlng.challenge_id = ss.challenge_id Group by Contests.contest_id, hacker_id, name having SUM(COALESCE(total_submissions,0)) != 0 or SUM(COALESCE(total_accepted_submissions,0)) != 0 or SUM(COALESCE(total_views,0)) != 0 or SUM(COALESCE(total_unique_views,0)) != 0 order by Contests.contest_id;

with cte_stud as ( select b.contest_id,a.hacker_id,a.name,c.challenge_id ,c.college_id from contests a join colleges b on a.contest_id = b.contest_id join challenges c on b.college_id = c.college_id ),cte_view as ( select ct.challenge_id,sum(v.total_views) as tv,sum(v.total_unique_views) as tuv from view_stats v join cte_stud ct on v.challenge_id = ct.challenge_id group by ct.challenge_id ),cte_sub as ( select ct.challenge_id,sum(total_submissions) as ts ,sum(total_accepted_submissions) as tas from submission_stats s join cte_stud ct on s.challenge_id = ct.challenge_id group by ct.challenge_id) select a.contest_id,a.hacker_id,a.name,sum(b.ts),sum(b.tas),sum(c.tv),sum(c.tuv) from cte_stud a left join cte_sub b on a.challenge_id = b.challenge_id left join cte_view c on a.challenge_id = c.challenge_id group by a.contest_id,a.hacker_id,a.name having (sum(b.ts)+sum(b.tas)+sum(c.tv)+sum(c.tuv)) > 0 order by a.contest_id

• SELECT con.contest_id, con.hacker_id, con.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 AS con JOIN Colleges AS col ON con.contest_id = col.contest_id JOIN Challenges AS ch ON col.college_id = ch.college_id LEFT JOIN View_Stats AS vs ON ch.challenge_id = vs.challenge_id LEFT JOIN Submission_Stats AS ss ON ch.challenge_id = ss.challenge_id GROUP BY con.contest_id, con.hacker_id, con.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 con.contest_id;

SELECT c.contest_id, c.hacker_id, c.name,

-- PARAMETROS GENERALES QUE DEBE SEGUIR EL RESTO DEL CODIGO, SIEMPRE QUE

   SUM(IFNULL(ss.total_submissions,0)) total_sub,
   SUM(IFNULL(ss.total_accepted_submissions,0)) total_acc,
   SUM(IFNULL(vs.total_views,0)) total_views,
   SUM(IFNULL(vs.total_unique_views,0)) total_uniq

FROM Contests c

-- camino -> Colegio -> encuesta -> desafios, para cada colegio hay una encuesta y para cada encuesta uno o multiples desafios (Union de tablas por un dato en comun)

JOIN Colleges uc ON c.contest_id=uc.contest_id -- Colegio -> encuesta JOIN Challenges ch ON uc.college_id=ch.college_id -- encuesta -> desafio

LEFT JOIN (SELECT challenge_id, SUM(total_submissions) total_submissions, SUM(total_accepted_submissions) total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id) ss ON ch.challenge_id = ss.challenge_id -- Agrupar por challenge id, uniendo filas de desafios con submissions

LEFT JOIN (SELECT challenge_id, SUM(total_views) total_views, SUM(total_unique_views) total_unique_views FROM View_Stats GROUP BY challenge_id) vs ON ch.challenge_id=vs.challenge_id -- Agrupar por challenge id, uniendo filas de desafios con la de envios

GROUP BY c.contest_id, c.hacker_id, c.name -- Agrupar segun como se relacionen

-- Filtrar los grupos donde almenos tengan un campo que no este vacio HAVING SUM(IFNULL(ss.total_submissions,0)+IFNULL(ss.total_accepted_submissions,0) +IFNULL(vs.total_views,0)+IFNULL(vs.total_unique_views,0))>0 ORDER BY c.contest_id;