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 contest_stat as
(
select
c.contest_id
, (total_submissions) as total_submissions
,(total_accepted_submissions) as total_accepted_submissions
,(total_views) as total_views
,(total_unique_views) as total_unique_views
from
Contests c
left join
Colleges cl
on
c.contest_id = cl.contest_id
left join
Challenges ch
on
cl.college_id = ch.college_id
/*
left join
View_Stats vs
on
ch.challenge_id = vs.challenge_id
left join
Submission_Stats ss
on
ch.challenge_id = ss.challenge_id
*/
left join
(
select
challenge_id
,sum(total_views) as total_views
,sum(total_unique_views) as total_unique_views
from
View_Stats
group by
challenge_id
) vin
on
ch.challenge_id = vin.challenge_id
left join
(
select
challenge_id
,sum(total_submissions) as total_submissions
,sum(total_accepted_submissions) as total_accepted_submissions
from
Submission_Stats
group by
challenge_id
) vin1
on
ch.challenge_id = vin1.challenge_id
)
select
c.contest_id
,c.hacker_id
,c.name
,sum(total_submissions)
,sum(total_accepted_submissions)
,sum(total_views)
,sum(total_unique_views)
from
Contests c
inner join
contest_stat cs
on
c.contest_id = cs.contest_id
group by
c.contest_id
,c.hacker_id
,c.name
having
sum(total_submissions)
+ sum(total_accepted_submissions)
+ sum(total_views)
+ sum(total_unique_views) > 0
order by
c.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 →
MS SQL Solution :
with contest_stat as ( select c.contest_id , (total_submissions) as total_submissions ,(total_accepted_submissions) as total_accepted_submissions ,(total_views) as total_views ,(total_unique_views) as total_unique_views from Contests c left join Colleges cl on c.contest_id = cl.contest_id left join Challenges ch
on cl.college_id = ch.college_id /* left join View_Stats vs on ch.challenge_id = vs.challenge_id left join Submission_Stats ss on ch.challenge_id = ss.challenge_id */ left join ( select challenge_id ,sum(total_views) as total_views ,sum(total_unique_views) as total_unique_views from View_Stats group by
challenge_id ) vin on ch.challenge_id = vin.challenge_id left join ( select challenge_id ,sum(total_submissions) as total_submissions ,sum(total_accepted_submissions) as total_accepted_submissions from Submission_Stats group by challenge_id ) vin1 on ch.challenge_id = vin1.challenge_id ) select c.contest_id ,c.hacker_id ,c.name ,sum(total_submissions) ,sum(total_accepted_submissions) ,sum(total_views) ,sum(total_unique_views) from Contests c inner join contest_stat cs on c.contest_id = cs.contest_id group by c.contest_id ,c.hacker_id ,c.name having sum(total_submissions) + sum(total_accepted_submissions) + sum(total_views) + sum(total_unique_views) > 0 order by c.contest_id