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 final_data as
(
select con.contest_id ,con.hacker_id, con.name,sum(ss.total_submissions) sum_total_sub ,sum(ss.total_accepted_submissions) sum_total_acc_sub,sum(vs.total_views) sum_total_vw,sum(vs.total_unique_views) sum_uni_vw
from contests con
inner join colleges cl on cl.contest_id = con.contest_id
inner join challenges ch on ch.college_id = cl.college_id
outer apply(
select sum(ss.total_submissions) total_submissions ,sum(ss.total_accepted_submissions) total_accepted_submissions from Submission_Stats ss where ss.challenge_id = ch.challenge_id group by ss.challenge_id
) ss
outer apply(
select sum(vs.total_views)total_views,sum(vs.total_unique_views) total_unique_views from View_Stats vs where vs.challenge_id = ch.challenge_id group by vs.challenge_id
) vs
group by con.contest_id ,con.hacker_id, con.name
)
select * from final_data
where sum_total_sub + sum_total_acc_sub+sum_total_vw+sum_uni_vw >0
order by 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 final_data as ( select con.contest_id ,con.hacker_id, con.name,sum(ss.total_submissions) sum_total_sub ,sum(ss.total_accepted_submissions) sum_total_acc_sub,sum(vs.total_views) sum_total_vw,sum(vs.total_unique_views) sum_uni_vw from contests con inner join colleges cl on cl.contest_id = con.contest_id inner join challenges ch on ch.college_id = cl.college_id outer apply( select sum(ss.total_submissions) total_submissions ,sum(ss.total_accepted_submissions) total_accepted_submissions from Submission_Stats ss where ss.challenge_id = ch.challenge_id group by ss.challenge_id ) ss outer apply( select sum(vs.total_views)total_views,sum(vs.total_unique_views) total_unique_views from View_Stats vs where vs.challenge_id = ch.challenge_id group by vs.challenge_id ) vs group by con.contest_id ,con.hacker_id, con.name ) select * from final_data where sum_total_sub + sum_total_acc_sub+sum_total_vw+sum_uni_vw >0 order by contest_id