This isn't really a SQL problem, its more of a data obersvation problem. If the problem is testing working with malformed data tables... just say that. Bad problem design, hackrank L

with challenge_data as (

select  clg.contest_id, 

        sum(total_submissions) ts, 
                    sum(total_accepted_submissions) tas

from colleges clg 

join challenges c on clg.college_id = c.college_id

join submission_stats st on st.challenge_id = c.challenge_id

group by clg.contest_id

),

views as (

select  clg.contest_id,

        sum(total_views) tv,

        sum(total_unique_views) tuv

    from view_stats vt 

join challenges c on c.challenge_id = vt.challenge_id

join colleges clg on clg.college_id = c.college_id

group by clg.contest_id

)

/Main combining select/

select c.contest_id,

    c.hacker_id, 

    c.name, 

    cd.ts,

    cd.tas,

    v.tv,

    v.tuv

from contests c join challenge_data cd on cd.contest_id = c.contest_id join views v on v.contest_id = c.contest_id order by c.contest_id

/*contest_id, hacker_id, who made the contest name, name of hacker who made the contest sum of total submissions, for that contest total_accepted_submissions total_views, total_unique_views order by contest_id */

with** challenge_data** as ( select clg.contest_id, sum(total_submissions) ts, sum(total_accepted_submissions) tas from colleges clg join challenges c on clg.college_id = c.college_id join submission_stats st on st.challenge_id = c.challenge_id group by clg.contest_id ),

views as ( select clg.contest_id, sum(total_views) tv, sum(total_unique_views) tuv from view_stats vt join challenges c on c.challenge_id = vt.challenge_id join colleges clg on clg.college_id = c.college_id group by clg.contest_id )

/* Main combining select*/ select c.contest_id, c.hacker_id, c.name, cd.ts, cd.tas, v.tv, v.tuv from contests c join challenge_data cd on cd.contest_id = c.contest_id join views v on v.contest_id = c.contest_id order by c.contest_id

    v.tuv

from contests c join challenge_data cd on cd.contest_id = c.contest_id join views v on v.contest_id = c.contest_id order by c.contest_id

To anyone who is wondering why simple inner join and performing sum() using group by does not work: One of the reason is, the set of challenge_ids in View_Stats does not equal to the set of challenge_ids in Submission_Stats. Therefore, using simple inner join to join stats data with the contest table will lead to missing records. It's not your fault.

MySQL SERVER

WITH cte1 AS (
    SELECT 
        ch.college_id,
        co.contest_id,
        ch.challenge_id
    FROM Colleges co
    
    JOIN Challenges ch 
    ON co.college_id = ch.college_id
),
cte2 AS (
SELECT ct.contest_id
, SUM(COALESCE(ve.total_views, 0)) AS total_views
, SUM(COALESCE(ve.total_unique_views, 0)) AS total_unique_views
FROM cte1 ct
    JOIN View_Stats ve 
    ON ct.challenge_id = ve.challenge_id
GROUP BY ct.contest_id

),
cte3 As
(
SELECT ct.contest_id
, SUM(COALESCE(su.total_submissions, 0)) AS total_submissions
, SUM(COALESCE(su.total_accepted_submissions, 0)) AS total_accepted_submissions
FROM cte1 ct   
  JOIN Submission_Stats su 
  ON ct.challenge_id = su.challenge_id
GROUP BY ct.contest_id
)
SELECT 
    cn.contest_id,
    cn.hacker_id,
    cn.name,
    SUM(total_submissions) As total_submissions,
    SUM(total_accepted_submissions) As total_accepted_submissions,
    SUM(total_views) As total_views,
    SUM(total_unique_views) As total_unique_views
FROM Contests cn

JOIN cte2 ct2 
ON cn.contest_id = ct2.contest_id

JOIN cte3 ct3
ON cn.contest_id = ct3.contest_id
GROUP BY cn.contest_id, cn.hacker_id, cn.name
HAVING SUM(total_submissions) + SUM(total_accepted_submissions) + SUM(total_views) + SUM(total_unique_views) > 0
ORDER BY cn.contest_id;