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.
select c.contest_id,
sum(total_submissions) as SOTS,
sum(total_accepted_submissions) as SOTAS
from colleges c
join challenges as cc on cc.college_id = c.college_id
join submission_stats as s on s.challenge_id = cc.challenge_id
group by c.contest_id
),
cte2 as (
select c.contest_id,
sum(total_views) as STV,
sum(total_unique_views) as STUV
from colleges c
join challenges as cc on c.college_id = cc.college_id
join view_stats as v on v.challenge_id = cc.challenge_id
group by c.contest_id
),
cte3 as (
select c.contest_id , hacker_id , name , SOTS , SoTAS, STV,STUV from contests c join cte as cc on cc.contest_id = c.contest_id join cte2 as ccc on ccc.contest_id = c.contest_id
)
select * from cte3 where sots !=0 and sotas!=0 and stv!=0 and stuv!=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 cte as (
group by c.contest_id
),
cte2 as (
),
cte3 as (
select c.contest_id , hacker_id , name , SOTS , SoTAS, STV,STUV from contests c join cte as cc on cc.contest_id = c.contest_id join cte2 as ccc on ccc.contest_id = c.contest_id
)
select * from cte3 where sots !=0 and sotas!=0 and stv!=0 and stuv!=0 order by contest_id