• + 0 comments

    ;with cte as (select vs.challenge_id,vs.total_views, vs.total_unique_views,ss.total_submissions,ss.total_accepted_submissions from view_stats vs join submission_stats ss on vs.challenge_id = ss.challenge_id ), cte1 as (select cs.contest_id,cs.hacker_id,cs.name,cl.college_id,ch.challenge_id from colleges cl join challenges ch on cl.college_id = ch.college_id join Contests cs on cl.contest_id = cs.contest_id), cte2 as ( select cte1.contest_id,cte1.hacker_id,cte1.name,cte.total_submissions,cte.total_accepted_submissions,cte.total_views,cte.total_unique_views from cte1 join cte on cte1.challenge_id = cte.challenge_id ) select contest_id,hacker_id,name,SUM(total_submissions),SUM(total_accepted_submissions),SUM(total_views),SUM(total_unique_views) from cte2 group by contest_id,hacker_id,name having SUM(total_submissions) + SUM(total_accepted_submissions) + SUM(total_views) + SUM(total_unique_views)>0

    order by contest_id