We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Join
- Interviews
- Discussions
Interviews
Interviews
Sort by
recency
|
1432 Discussions
|
Please Login in order to post a comment
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 c.contest_id, c.hacker_id, c.name,
-- PARAMETROS GENERALES QUE DEBE SEGUIR EL RESTO DEL CODIGO, SIEMPRE QUE
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;