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.
WITH c1 AS (
SELECT
con.contest_id,
SUM(NVL(total_submissions, 0)) AS total_sub_sum,
SUM(NVL(total_accepted_submissions, 0)) AS tot_acc_sub_sum
FROM
Contests con
JOIN Colleges col ON con.contest_id = col.contest_id
JOIN Challenges chl ON col.college_id = chl.college_id
LEFT JOIN Submission_Stats subs ON chl.challenge_id = subs.challenge_id
GROUP BY con.contest_id
),
c2 AS (
SELECT
con.contest_id,
SUM(NVL(total_views, 0)) AS tot_views_sum,
SUM(NVL(total_unique_views, 0)) AS tot_uniq_views_sum
FROM
Contests con
JOIN Colleges col ON con.contest_id = col.contest_id
JOIN Challenges chl ON col.college_id = chl.college_id
LEFT JOIN View_Stats vws ON chl.challenge_id = vws.challenge_id
GROUP BY con.contest_id
)
SELECT
cts.contest_id,hacker_id,name,c1.total_sub_sum,c1.tot_acc_sub_sum,c2.tot_views_sum,c2.tot_uniq_views_sum
FROM
Contests cts
JOIN c1 ON cts.contest_id = c1.contest_id
JOIN c2 ON cts.contest_id = c2.contest_id order by cts.contest_id;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Interviews
You are viewing a single comment's thread. Return to all comments →
WITH c1 AS ( SELECT con.contest_id, SUM(NVL(total_submissions, 0)) AS total_sub_sum, SUM(NVL(total_accepted_submissions, 0)) AS tot_acc_sub_sum FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges chl ON col.college_id = chl.college_id LEFT JOIN Submission_Stats subs ON chl.challenge_id = subs.challenge_id GROUP BY con.contest_id ), c2 AS ( SELECT con.contest_id, SUM(NVL(total_views, 0)) AS tot_views_sum, SUM(NVL(total_unique_views, 0)) AS tot_uniq_views_sum FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges chl ON col.college_id = chl.college_id LEFT JOIN View_Stats vws ON chl.challenge_id = vws.challenge_id GROUP BY con.contest_id ) SELECT cts.contest_id,hacker_id,name,c1.total_sub_sum,c1.tot_acc_sub_sum,c2.tot_views_sum,c2.tot_uniq_views_sum FROM Contests cts JOIN c1 ON cts.contest_id = c1.contest_id JOIN c2 ON cts.contest_id = c2.contest_id order by cts.contest_id;