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
|
1240 Discussions
|
Please Login in order to post a comment
with cte as( select
ch.college_id as college_id, ch.challenge_id as challenge_id, sum(s.total_submissions) as c1 , sum(s.total_accepted_submissions) as c2 from challenges as ch join Submission_Stats as s on s.challenge_id = ch.challenge_id GROUP BY ch.college_id, ch.challenge_id ) , cte_2 as( select c.contest_id as contest_id, sum(cte.c1) as c1, sum(cte.c2) as c2 from cte join colleges as c on c.college_id = cte.college_id group by c.contest_id ) , cte_3 as( select
ch.college_id as college_id, ch.challenge_id as challenge_id, sum(v.total_views) as c3, sum(v.total_unique_views) as c4 from challenges as ch join view_stats as v on v.challenge_id = ch.challenge_id GROUP BY ch.college_id, ch.challenge_id ) , cte_4 as( select c.contest_id as contest_id, sum(cte_3.c3) as c3, sum(cte_3.c4) as c4 from cte_3 join colleges as c on c.college_id = cte_3.college_id group by c.contest_id ) select c.contest_id, c.hacker_id, c.name, cte_2.c1, cte_2.c2, cte_4.c3, cte_4.c4 from contests as c join cte_2 on c.contest_id = cte_2.contest_id join cte_4 on c.contest_id = cte_4.contest_id where c1+c2+c3+c4 > 0 ORDER BY c.contest_id
In a more organized way
Please fine the easiest solution in my sql
select c.contest_id, c.hacker_id,c.name, sum(ss.total_submissions), sum(ss.total_accepted_submissions),sum(vs.total_views), sum(vs.total_unique_views) from contests c left join colleges co on c.contest_id= co.contest_id left join challenges ch on ch.college_id=co.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) vs on ch.challenge_id=vs.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 ) ss on ch.challenge_id=ss.challenge_id group by c.contest_id,c.hacker_id,c.name having sum(ss.total_submissions)+ sum(ss.total_accepted_submissions)+ sum(vs.total_views)+ sum(vs.total_unique_views) >0 order by c.contest_id asc
what is wrong with this?
WITH cte1 AS ( SELECT colleges.college_id, contest_id, challenge_id FROM colleges INNER JOIN challenges ON colleges.college_id = challenges.college_id ), cte2 AS ( SELECT college_id, contest_id, a.challenge_id, total_views, total_unique_views, total_submissions, total_accepted_submissions FROM cte1 AS a JOIN view_stats b ON a.challenge_id = b.challenge_id JOIN submission_stats c ON b.challenge_id = c.challenge_id ), cte3 AS ( SELECT contest_id, SUM(total_submissions) AS total_submissions, SUM(total_accepted_submissions) AS total_accepted_submissions, SUM(total_views) AS total_views, SUM(total_unique_views) AS total_unique_views FROM cte2 GROUP BY contest_id ) SELECT a.contest_id, total_submissions, total_accepted_submissions, total_views, total_unique_views FROM cte3 a JOIN contests b ON a.contest_id = b.contest_id order by contest_id;