WITH CTE_TotalSubmissions AS ( SELECT challenge_id, SUM(total_submissions) AS total_submissions_per_challenge_id, SUM(total_accepted_submissions) AS total_accepted_submissions_per_challenge_id FROM Submission_Stats GROUP BY challenge_id ), CTE_TotalViews AS ( SELECT challenge_id, SUM(total_views) AS total_views_per_challenge_id, SUM(total_unique_views) AS total_unique_views_per_challenge_id FROM View_Stats GROUP BY challenge_id ), CTE_TotalCollegeID AS ( SELECT cl.contest_id, SUM(cts.total_submissions_per_challenge_id) AS sum_submissions, SUM(cts.total_accepted_submissions_per_challenge_id) AS sum_accepted_submissions, SUM(ctv.total_views_per_challenge_id) AS sum_views, SUM(ctv.total_unique_views_per_challenge_id) AS sum_unique_views FROM Challenges c LEFT JOIN CTE_TotalSubmissions cts ON cts.challenge_id = c.challenge_id LEFT JOIN CTE_TotalViews ctv ON ctv.challenge_id = c.challenge_id LEFT JOIN Colleges cl ON cl.college_id = c.college_id GROUP BY cl.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tot.sum_submissions, tot.sum_accepted_submissions, tot.sum_views, tot.sum_unique_views FROM Contests con LEFT JOIN CTE_TotalCollegeID tot ON tot.contest_id = con.contest_id WHERE con.contest_id i use CTE i think use it make code more readable and easy to understand ps; sorry about my english:)

tot.sum_submissions <> 0 OR
tot.sum_accepted_submissions <> 0 OR
tot.sum_views <> 0 OR
tot.sum_unique_views <> 0

ORDER BY con.contest_id;

Simple and faster query as below

with cte1 as ( select a.contest_id s, a.hacker_id t, a.name u,
sum(d.total_submissions) w, sum(d.total_accepted_submissions) x from contests a left join colleges b on a.contest_id = b.contest_id left join challenges c on b.college_id = c.college_id left join Submission_Stats d on c.challenge_id = d.challenge_id

group by a.contest_id, a.hacker_id, a.name), cte2 as ( select e.contest_id l, e.hacker_id m, e.name n,
sum(h.total_views) y, sum(h.total_unique_views) z from contests e left join colleges f on e.contest_id = f.contest_id left join challenges g on f.college_id = g.college_id left join view_stats h on g.challenge_id = h.challenge_id

group by e.contest_id, e.hacker_id, e.name )

select cte1.s, cte1.t, cte1.u, cte1.w, cte1.x, cte2.y, cte2.z from cte1, cte2 where cte1.s = cte2.l and cte1.t = cte2.m and cte1.u = cte2.n and cte1.w+cte1.x+cte2.y+cte2.z>0 order by cte1.s asc

I'm not sure why this logic won't work. More generally, this looks like an edge case testing interview question (e.g. for nulls etc) and don't see much value with

WITH contest_stats AS ( -- First aggregate all metrics at the contest level SELECT co.contest_id, SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions, SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(vs.total_views, 0)) AS total_views, SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views FROM colleges co LEFT JOIN challenges ch ON ch.college_id = co.college_id LEFT JOIN view_stats vs ON vs.challenge_id = ch.challenge_id LEFT JOIN submission_stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id )

-- Join back to get contest details SELECT c.contest_id, c.hacker_id, c.name, cs.total_submissions, cs.total_accepted_submissions, cs.total_views, cs.total_unique_views FROM contests c JOIN contest_stats cs ON c.contest_id = cs.contest_id WHERE (cs.total_submissions + cs.total_accepted_submissions+cs.total_views+cs.total_unique_views) > 0 ORDER BY c.contest_id;

Took a little advice from previous posts here, but arrived at a solution finally.

Careful with the null values and group all to contest level with college table before doing the final join with contests.

SELECT con.contest_id,
    con.hacker_id,
    con.name,
    ts,
    tas,
    tv,
    tuv
FROM contests AS con
INNER JOIN (
    SELECT coll.contest_id,
        SUM(COALESCE(ts,0)) AS ts,
        SUM(COAlESCE(tas,0)) AS tas,
        SUM(COALESCE(tv,0)) AS tv,
        SUM(COALESCE(tuv,0)) AS tuv
    FROM challenges AS chall
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_submissions,0)) AS ts,
                SUM(COALESCE(total_accepted_submissions,0)) AS tas
            FROM submission_stats AS sstats
            GROUP BY challenge_id
            ) AS sstats
    ON chall.challenge_id = sstats.challenge_id
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_views,0)) AS tv,
                SUM(COALESCE(total_unique_views,0)) AS tuv
            FROM view_stats AS vstats
            GROUP BY challenge_id
            ) AS vstats
    ON chall.challenge_id = vstats.challenge_id
    LEFT JOIN colleges AS coll
    ON chall.college_id = coll.college_id
    GROUP BY coll.contest_id) AS fstats
ON con.contest_id = fstats.contest_id
ORDER BY con.contest_id
;

Because I didn't fully understand INNER JOIN, I spent some time on this. INNER JOINs are chained, so if any condition is not met, the entire row is discarded. Therefore, data like 'TV=NULL, TUV=NULL, TS=8, TAS=4' would be discarded. Below is my answer.

SELECT
    Co.contest_id,
    Co.hacker_id,
    Co.name,
    TS,
    TAS,
    TV,
    TUV
FROM Contests AS Co
JOIN (
    SELECT
        Col.contest_id AS contest_id,
        SUM(TV) AS TV,
        SUM(TUV) AS TUV,
        SUM(TS) AS TS,
        SUM(TAS) AS TAS
    FROM Colleges AS Col
    JOIN Challenges AS Ch ON Ch.college_id = Col.college_id 
    LEFT JOIN (
        SELECT
            challenge_id,
            SUM(total_views) AS TV,
            SUM(total_unique_views) AS TUV
        FROM View_Stats GROUP BY challenge_id
    ) AS VS ON VS.challenge_id = Ch.challenge_id
    LEFT JOIN (
        SELECT
            challenge_id,
            SUM(total_submissions) AS TS,
            SUM(total_accepted_submissions) AS TAS
        FROM Submission_Stats GROUP BY challenge_id
    ) AS SS ON SS.challenge_id = Ch.challenge_id
    GROUP BY Col.contest_id
) AS CD ON CD.contest_id = Co.contest_id
WHERE TV + TUV + TS + TAS > 0
ORDER BY Co.contest_id