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_stud as (
select b.contest_id,a.hacker_id,a.name,c.challenge_id ,c.college_id from contests a join colleges b
on a.contest_id = b.contest_id join challenges c on
b.college_id = c.college_id
),cte_view as (
select ct.challenge_id,sum(v.total_views) as tv,sum(v.total_unique_views) as tuv from view_stats v join cte_stud ct
on v.challenge_id = ct.challenge_id group by ct.challenge_id
),cte_sub as (
select ct.challenge_id,sum(total_submissions) as ts ,sum(total_accepted_submissions) as tas from submission_stats s
join cte_stud ct on s.challenge_id = ct.challenge_id group by
ct.challenge_id)
select a.contest_id,a.hacker_id,a.name,sum(b.ts),sum(b.tas),sum(c.tv),sum(c.tuv) from cte_stud a left join cte_sub b on a.challenge_id = b.challenge_id left join cte_view c on a.challenge_id = c.challenge_id
group by a.contest_id,a.hacker_id,a.name having (sum(b.ts)+sum(b.tas)+sum(c.tv)+sum(c.tuv)) > 0 order by a.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_stud as ( select b.contest_id,a.hacker_id,a.name,c.challenge_id ,c.college_id from contests a join colleges b on a.contest_id = b.contest_id join challenges c on b.college_id = c.college_id ),cte_view as ( select ct.challenge_id,sum(v.total_views) as tv,sum(v.total_unique_views) as tuv from view_stats v join cte_stud ct on v.challenge_id = ct.challenge_id group by ct.challenge_id ),cte_sub as ( select ct.challenge_id,sum(total_submissions) as ts ,sum(total_accepted_submissions) as tas from submission_stats s join cte_stud ct on s.challenge_id = ct.challenge_id group by ct.challenge_id) select a.contest_id,a.hacker_id,a.name,sum(b.ts),sum(b.tas),sum(c.tv),sum(c.tuv) from cte_stud a left join cte_sub b on a.challenge_id = b.challenge_id left join cte_view c on a.challenge_id = c.challenge_id group by a.contest_id,a.hacker_id,a.name having (sum(b.ts)+sum(b.tas)+sum(c.tv)+sum(c.tuv)) > 0 order by a.contest_id