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,
sum(isnull(total_submissions,0)) as total_submission,
sum(isnull(total_accepted_submissions,0)) as total_accepted_submission
from submission_stats
group by challenge_id),
cte1 as( select challenge_id,
SUM(ISNULL(total_views, 0)) AS total_views,
SUM(ISNULL(total_unique_views, 0)) AS total_unique_views
FROM view_stats
GROUP BY challenge_id)
select con.contest_id, con.hacker_id, con.name,
SUM(ISNULL(c.total_submission, 0)) AS total_submission,
SUM(ISNULL(c.total_accepted_submission, 0)) AS total_accepted_submission,
SUM(ISNULL(ct.total_views, 0)) AS total_views,
SUM(ISNULL(ct.total_unique_views, 0)) AS total_unique_view
from contests as con join colleges as col
on con.contest_id=col.contest_id
join challenges as ch on col.college_id=ch.college_id
left join cte as c on ch.challenge_id=c.challenge_id
left join cte1 as ct on ch.challenge_id=ct.challenge_id
group by con.contest_id, con.hacker_id, con.name
having SUM(ISNULL(c.total_submission, 0)) +
SUM(ISNULL(c.total_accepted_submission, 0))+ SUM(ISNULL(ct.total_views, 0))+
SUM(ISNULL(ct.total_unique_views, 0)) >0
order by con.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, sum(isnull(total_submissions,0)) as total_submission, sum(isnull(total_accepted_submissions,0)) as total_accepted_submission from submission_stats group by challenge_id), cte1 as( select challenge_id, SUM(ISNULL(total_views, 0)) AS total_views, SUM(ISNULL(total_unique_views, 0)) AS total_unique_views FROM view_stats GROUP BY challenge_id) select con.contest_id, con.hacker_id, con.name, SUM(ISNULL(c.total_submission, 0)) AS total_submission, SUM(ISNULL(c.total_accepted_submission, 0)) AS total_accepted_submission, SUM(ISNULL(ct.total_views, 0)) AS total_views, SUM(ISNULL(ct.total_unique_views, 0)) AS total_unique_view from contests as con join colleges as col on con.contest_id=col.contest_id join challenges as ch on col.college_id=ch.college_id left join cte as c on ch.challenge_id=c.challenge_id left join cte1 as ct on ch.challenge_id=ct.challenge_id group by con.contest_id, con.hacker_id, con.name having SUM(ISNULL(c.total_submission, 0)) + SUM(ISNULL(c.total_accepted_submission, 0))+ SUM(ISNULL(ct.total_views, 0))+ SUM(ISNULL(ct.total_unique_views, 0)) >0 order by con.contest_id;