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 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;
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 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;