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 Colleges.Contest_id, SUM(cr.Total_unique_views) As SumTotal_unique_views,SUM(cr.Total_views) As SumTotal_views
From Challenges As c
Join Colleges on Colleges.College_id =c.College_id
Cross apply (Select * From View_Stats As vs Where vs.Challenge_id=c.Challenge_id) As Cr
Group by Colleges.Contest_id
),
Cte1 As (
Select Colleges.Contest_id, SUM(Cr1.Total_submissions) As SumTotal_submissions,
SUM(Cr1.Total_accepted_submissions) As SumTotal_accepted_submissions
From Challenges As c
Join Colleges on Colleges.College_id =c.College_id
outer apply (Select * From Submission_Stats As ss Where ss.Challenge_id=c.Challenge_id) As Cr1
Group by Colleges.Contest_id
)
Select Contests.Contest_id,Contests.Hacker_id,Contests.Name,
IsNull(Cte1.SumTotal_submissions,0) As SumTotal_submissions,IsNull(Cte1.SumTotal_accepted_submissions,0) As SumTotal_accepted_submissions,
IsNull(Cte.SumTotal_views,0)As SumTotal_views,IsNull(Cte.SumTotal_unique_views,0) As SumTotal_unique_views from
Cte join Cte1
on Cte.Contest_id = Cte1.Contest_id
Join Contests on Contests.Contest_id = Cte1.Contest_id
Where SumTotal_submissions is not null Or SumTotal_accepted_submissions is not null or SumTotal_views is not null or SumTotal_unique_views is not null
order by Contests.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 →
With Cte As ( Select Colleges.Contest_id, SUM(cr.Total_unique_views) As SumTotal_unique_views,SUM(cr.Total_views) As SumTotal_views From Challenges As c Join Colleges on Colleges.College_id =c.College_id Cross apply (Select * From View_Stats As vs Where vs.Challenge_id=c.Challenge_id) As Cr Group by Colleges.Contest_id ), Cte1 As ( Select Colleges.Contest_id, SUM(Cr1.Total_submissions) As SumTotal_submissions, SUM(Cr1.Total_accepted_submissions) As SumTotal_accepted_submissions From Challenges As c Join Colleges on Colleges.College_id =c.College_id outer apply (Select * From Submission_Stats As ss Where ss.Challenge_id=c.Challenge_id) As Cr1 Group by Colleges.Contest_id )
Select Contests.Contest_id,Contests.Hacker_id,Contests.Name, IsNull(Cte1.SumTotal_submissions,0) As SumTotal_submissions,IsNull(Cte1.SumTotal_accepted_submissions,0) As SumTotal_accepted_submissions, IsNull(Cte.SumTotal_views,0)As SumTotal_views,IsNull(Cte.SumTotal_unique_views,0) As SumTotal_unique_views from Cte join Cte1 on Cte.Contest_id = Cte1.Contest_id Join Contests on Contests.Contest_id = Cte1.Contest_id Where SumTotal_submissions is not null Or SumTotal_accepted_submissions is not null or SumTotal_views is not null or SumTotal_unique_views is not null order by Contests.Contest_id