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

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

This problem seemed simple at first but it really makes you think hard about how you're using your JOINs like what would happen if you FULL JOIN 2 tables with different number of rows for their common ID column. Also a good case for using COALESCE.

Answer:

WITH st AS (
    SELECT col.contest_id
    ,SUM(st.total_views) total_views
    ,SUM(st.total_unique_views) total_unique_views
    ,SUM(st.total_submissions) total_submissions
    ,SUM(st.total_accepted_submissions) total_accepted_submissions
    FROM colleges col
    INNER JOIN (
        SELECT college_id
            ,SUM(st.total_views) total_views
            ,SUM(st.total_unique_views) total_unique_views
            ,SUM(st.total_submissions) total_submissions
            ,SUM(st.total_accepted_submissions) total_accepted_submissions
        FROM challenges cha
        INNER JOIN (
            SELECT COALESCE(v.challenge_id, s.challenge_id) AS challenge_id
            ,COALESCE(
                total_views
                ,0
            ) AS total_views
            ,COALESCE(
                total_unique_views
                ,0
            ) AS total_unique_views
            ,COALESCE(
                total_submissions
                ,0
            ) AS total_submissions
            ,COALESCE(
                total_accepted_submissions
                ,0
            ) AS total_accepted_submissions
            FROM (
                SELECT challenge_id
                ,SUM(total_views) AS total_views
                ,SUM(total_unique_views) AS total_unique_views
                FROM view_stats v
                GROUP BY challenge_id
            ) v
            FULL OUTER 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
            ) s
            ON v.challenge_id = s.challenge_id
        ) st
        ON cha.challenge_id = st.challenge_id
        GROUP BY college_id
    ) st
    ON col.college_id = st.college_id
    GROUP BY col.contest_id
)
SELECT con.contest_id
,con.hacker_id
,con.name
,st.total_submissions
,st.total_accepted_submissions
,st.total_views
,total_unique_views
FROM contests con
INNER JOIN st
ON con.contest_id = st.contest_id
ORDER BY con.contest_id

Is this problem not accepting CTE's?

The problem here is: There are more than 2 aggreations tables (The view table and the submission table). Therefore, we cannot join all of them together at once because JOIN clause will make the value duplicate resulting in wrong calculation. For example: in for challenge_id 145: There are 4 rows consist 145 in View_Stats table, but there are only 1 row consists 145 in Submission_Stats. When we join all of them at once, the row in submssion table will be quadrupped resulting in wrong sum for total_submissions. So we must aggregate each table separately before joinning all of them together. WITH Views_table AS (SELECT ch.challenge_id, SUM(Total_views) Totals_views, SUM(Total_unique_views) Total_unique_views FROM Challenges ch JOIN View_stats vw ON ch.challenge_id = vw.challenge_id GROUP BY ch.challenge_id), Submissions_table AS (SELECT ch.challenge_id, SUM(Total_submissions) Total_submissions, SUM(Total_accepted_submissions) Total_accepted_submissions FROM Challenges ch JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY ch.challenge_id) SELECT ct.contest_id, hacker_id, name, SUM(Total_submissions), SUM(Total_accepted_submissions), SUM(Totals_views), SUM(Total_unique_views) FROM contests ct JOIN colleges cl ON ct.Contest_id = cl.contest_id JOIN challenges ch ON ch.college_id = cl.college_id LEFT JOIN Views_table v ON ch.challenge_id = v.challenge_id LEFT JOIN Submissions_table s ON ch.challenge_id = s.challenge_id GROUP BY ct.contest_id, hacker_id, name ORDER BY ct.contest_id