To save you 5 minutes in recreating the sample dataset, here it is for MySQL:

create table Contests ( contest_id INT, hacker_id INT, name VARCHAR(200) );

insert into Contests (contest_id, hacker_id, name) values (66406, 17973, "Rose"), (66556, 79153, "Angela"), (94828, 80275, "Frank");

create table Colleges( college_id INT, contest_id INT );

insert into Colleges (college_id, contest_id) values (11219, 66406), (32473, 66556), (56685, 94828);

create table Challenges ( challenge_id INT, college_id INT );

insert into Challenges (challenge_id, college_id) values (18765, 11219), (47127, 11219), (60292, 32473), (72974, 56685);

create table View_Stats ( challenge_id INT, total_views INT, total_unique_views INT );

insert into View_Stats (challenge_id, total_views, total_unique_views) values (47127, 26, 19), (47127, 15, 14), (18765, 43, 10), (18765, 72, 13), (75516, 35, 17), (60292, 11, 10), (72974, 41, 15), (75516, 75, 11);

create table Submission_Stats ( challenge_id INT, total_submissions INT, total_accepted_submissions INT );

insert into Submission_Stats (challenge_id, total_submissions, total_accepted_submissions) values (75516, 34, 12), (47127, 27, 10), (47127, 56, 18), (75516, 74, 12), (75516, 83, 8), (72974, 68, 24), (72974, 82, 14), (47127, 28, 11);

The relationship: contest 1---* college 1---*challenges For each challenge, there could be no views(View_Stats) or submissions(Submission_Stats), therefore, we use left join with these two tables.

select con.contest_id,
        con.hacker_id, 
        con.name, 
        sum(total_submissions), 
        sum(total_accepted_submissions), 
        sum(total_views), sum(total_unique_views)
from contests con 
join colleges col on con.contest_id = col.contest_id 
join challenges cha on  col.college_id = cha.college_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) vs on cha.challenge_id = vs.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) ss on cha.challenge_id = ss.challenge_id
    group by con.contest_id, con.hacker_id, con.name
        having sum(total_submissions)!=0 or 
                sum(total_accepted_submissions)!=0 or
                sum(total_views)!=0 or
                sum(total_unique_views)!=0
            order by contest_id;

Using CTE to group the Stats and Submission tables first is crucial, after that, its just a matter of left joining the results.

WITH SUM_View_Stats AS (
SELECT challenge_id
    , total_views = sum(total_views)
    , total_unique_views = sum(total_unique_views)
FROM View_Stats 
GROUP BY challenge_id
)
,SUM_Submission_Stats AS (
SELECT challenge_id
    , total_submissions = sum(total_submissions)
    , total_accepted_submissions = sum(total_accepted_submissions)
FROM Submission_Stats 
GROUP BY challenge_id
)

SELECT    con.contest_id
        , con.hacker_id
        , con.name
        , SUM(total_submissions)
        , sum(total_accepted_submissions)
        , sum(total_views)
        , sum(total_unique_views)
FROM Contests con
INNER JOIN Colleges col
    ON con.contest_id = col.contest_id
INNER JOIN Challenges cha
    ON cha.college_id = col.college_id
LEFT JOIN SUM_View_Stats vs
    ON vs.challenge_id = cha.challenge_id
LEFT JOIN SUM_Submission_Stats ss
    ON ss.challenge_id = cha.challenge_id
GROUP BY con.contest_id,con.hacker_id,con.name
HAVING (SUM(total_submissions)
        +sum(total_accepted_submissions)
        +sum(total_views)
        +sum(total_unique_views)) <> 0
ORDER BY con.contest_ID

Hints:

• Not necessary that all CHALLENGE_ID have a value in VIEW_STATS and/or SUBMISSION_STATS. (LEFT JOIN)

• Treat each table SUMs individually (VIEW_STATS[total_views, total_unique_views], SUBMISSION_STATS[total_submissions, total_accepted_submissions])

To save some trouble...

http://sqlfiddle.com/#!9/10d6ce/34/0

This is more of an exercise about aggregations than joins

You have to create multiple aggregations, SUM the different values grouped by contest, convert null values into zero (for the where clause) and be careful about duplication (the only part where I can see join is actually an important thing here)

I believe this exercise should be in a different domain.

Problem can be explained in a more better way.

My Solution in oracle:

select t1.contest_id, c.hacker_id, c.name, t1.st_submissions1, t1.sta_submissions1, t1.st_views1, t1.stu_views1 from (select c.contest_id, sum(t.st_views) st_views1, sum(t.stu_views) stu_views1, sum(t.st_submissions) st_submissions1, sum(t.sta_submissions) sta_submissions1 from (select v.college_id, v.st_views, v.stu_views, s.st_submissions, s.sta_submissions from (select c.college_id, sum(v.total_views) st_views, sum(v.total_unique_views) stu_views from challenges c, view_stats v where c.challenge_id = v.challenge_id group by c.college_id) v, (select c.college_id, sum(s.total_submissions) st_submissions, sum(s.total_accepted_submissions) sta_submissions from challenges c, submission_stats s where c.challenge_id = s.challenge_id group by c.college_id) s where v.college_id=s.college_id and (v.st_views<>0 or v.stu_views<>0 or s.st_submissions<>0 or s.sta_submissions<>0)) t, colleges c where t.college_id=c.college_id group by c.contest_id) t1, contests c where t1.contest_id = c.contest_id order by t1.contest_id;

Need help: I do this, in mysql, and i don't get why is a wrong answer

select con.contest_id, con.hacker_id, con.name, sum(ss.total_submissions), sum(ss.total_accepted_submissions), sum(vs.total_views), sum(vs.total_unique_views) from contests con inner join colleges col on con.contest_id = col.contest_id inner join challenges cha on col.college_id = cha.college_id inner join view_stats vs on cha.challenge_id = vs.challenge_id inner join submission_stats ss on cha.challenge_id = ss.challenge_id group by con.contest_id, con.hacker_id, con.name having sum(ss.total_submissions) > 0 or sum(ss.total_accepted_submissions)>0 or sum(vs.total_views)>0 or sum(vs.total_unique_views)>0 order by con.contest_id