• + 0 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