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
|
1211 Discussions
|
Please Login in order to post a comment
For MYSQL
SELECT con.contest_id, con.hacker_id, con.name, COALESCE(SUM(sg.total_submissions), 0) AS total_submissions, COALESCE(SUM(sg.total_accepted_submissions), 0) AS total_accepted_submissions, COALESCE(SUM(vg.total_views), 0) AS total_views, COALESCE(SUM(vg.total_unique_views), 0) AS total_unique_views FROM Contests con INNER JOIN Colleges col ON con.contest_id = col.contest_id INNER JOIN Challenges cha ON col.college_id = cha.college_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) sg ON cha.challenge_id = sg.challenge_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) vg ON cha.challenge_id = vg.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING COALESCE(SUM(sg.total_submissions), 0) != 0 OR COALESCE(SUM(sg.total_accepted_submissions), 0) != 0 OR COALESCE(SUM(vg.total_views), 0) != 0 OR COALESCE(SUM(vg.total_unique_views), 0) != 0 ORDER BY con.contest_id;
with cte as ( select challenge_id, COALESCE(SUM(total_views), 0) AS view_sum, COALESCE(SUM(total_unique_views), 0) AS view_uq_sum from view_stats group by challenge_id),
cte1 as (select challenge_id, COALESCE(SUM(total_submissions), 0) AS sub_sum, COALESCE(SUM(total_accepted_submissions), 0) AS sub_acc_sum from Submission_Stats group by challenge_id),
cte2 as (select COALESCE(c.challenge_id,d.challenge_id) as ch1, COALESCE(d.challenge_id,c.challenge_id) as ch2, view_sum, view_uq_sum, sub_sum, sub_acc_sum from cte c full outer join cte1 d on c.challenge_id = d.challenge_id)
select co.contest_id, hacker_id, name, sum(sub_sum) as w, sum( sub_acc_sum) as x, sum( view_sum) as y, sum(view_uq_sum) as z from challenges a join cte2 b on a.challenge_id = ch1 join colleges co on a.college_id = co.college_id join contests con on co.contest_id = con.contest_id group by co.contest_id, name, hacker_id having sum(sub_sum) <> 0 or sum( sub_acc_sum) <> 0 or sum( view_sum) <> 0 or sum(view_uq_sum) <> 0 order by co.contest_id;
SELECT con.contest_id, con.hacker_id, con.name, SUM(sg.total_submissions) as total_submissions, SUM(sg.total_accepted_submissions) as total_accepted_submissions, SUM(vg.total_views) as total_views, SUM(vg.total_unique_views) as total_unique_views FROM Contests con INNER JOIN Colleges col ON con.contest_id = col.contest_id INNER JOIN Challenges cha ON col.college_id = cha.college_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 ) sg ON cha.challenge_id = sg.challenge_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 ) vg ON cha.challenge_id = vg.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING SUM(sg.total_submissions) != 0 OR SUM(sg.total_accepted_submissions) != 0 OR SUM(vg.total_views) != 0 OR SUM(vg.total_unique_views) != 0 ORDER BY con.contest_id;
`WITH total_submit as ( SELECT coll.contest_id, SUM(ss.total_submissions) AS sumts, SUM(ss.total_accepted_submissions) AS sumtas FROM Colleges coll INNER JOIN Challenges chall ON chall.college_id = coll.college_id INNER JOIN Submission_Stats ss ON chall.challenge_id = ss.challenge_id GROUP BY coll.contest_id ), total_view as ( SELECT coll.contest_id, SUM(vs.total_views) AS sumtv, SUM(vs.total_unique_views) AS sumtuv FROM Colleges coll INNER JOIN Challenges chall ON chall.college_id = coll.college_id INNER JOIN View_Stats vs ON chall.challenge_id = vs.challenge_id GROUP BY coll.contest_id ) SELECT cont.contest_id, cont.hacker_id, cont.name, ts.sumts, ts.sumtas, tv.sumtv, tv.sumtuv FROM Contests cont INNER JOIN total_submit ts ON ts.contest_id = cont.contest_id INNER JOIN total_view tv ON tv.contest_id = cont.contest_id WHERE ts.sumts > 0 OR ts.sumtas > 0 OR tv.sumtv > 0 OR tv.sumtuv > 0 ORDER BY cont.contest_id