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 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
Interviews
You are viewing a single comment's thread. Return to all 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