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
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