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
An unexpected error occurred. Please try reloading the page. If problem persists, please contact support@hackerrank.com
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