You are viewing a single comment's thread. Return to all comments →
(SQL Server)
with cte_sum_vs as ( select ct.contest_id ,ct.hacker_id ,ct.name ,sum(vs.total_views) total_views ,sum(vs.total_unique_views) total_unique_views from view_stats vs inner join challenges ch on vs.challenge_id = ch.challenge_id inner join colleges cl on ch.college_id = cl.college_id inner join contests ct on cl.contest_id = ct.contest_id group by ct.hacker_id, ct.contest_id, ct.name ), cte_sum_ss as ( select ct.contest_id ,ct.hacker_id ,ct.name ,sum(ss.total_submissions) total_submissions ,sum(ss.total_accepted_submissions) total_accepted_submissions from submission_stats ss inner join challenges ch on ss.challenge_id = ch.challenge_id inner join colleges cl on ch.college_id = cl.college_id inner join contests ct on cl.contest_id = ct.contest_id group by ct.hacker_id, ct.contest_id, ct.name ) select ss.contest_id ,ss.hacker_id ,ss.name ,ss.total_submissions ,ss.total_accepted_submissions ,vs.total_views ,vs.total_unique_views from cte_sum_ss ss inner join cte_sum_vs vs on ss.hacker_id = vs.hacker_id where 1=1 and (ss.total_submissions + ss.total_accepted_submissions + vs.total_views + vs.total_unique_views) > 0 order by ss.contest_id
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 →
(SQL Server)