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