SELECT c.contest_id, c.hacker_id, c.name, COALESCE(SUM(ss.total_submissions), 0) AS total_submissions, COALESCE(SUM(ss.total_accepted_submissions), 0) AS total_accepted_submissions, COALESCE(SUM(v.total_views), 0) AS total_views, COALESCE(SUM(v.total_unique_views), 0) 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 ( 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 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 ) v ON ch.challenge_id = v.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING SUM(ss.total_submissions) > 0 OR SUM(ss.total_accepted_submissions) > 0 OR SUM(v.total_views) > 0 OR SUM(v.total_unique_views) > 0 ORDER BY c.contest_id;

can anyone fix my query please mssqlserver :( WITH SUM_SUB AS ( SELECT challenge_id, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(total_submissions, 0)) AS total_submissions FROM Submission_Stats GROUP BY challenge_id ), SUM_VIEW AS ( SELECT challenge_id, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM View_Stats GROUP BY challenge_id ), CON_VIEW AS ( SELECT
con.contest_id, con.hacker_id, con.name, col.college_id, cal.challenge_id, total_views, total_unique_views FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges cal ON col.college_id = cal.college_id
JOIN SUM_VIEW ss ON cal.challenge_id = ss.challenge_id ), CON_SUB AS ( SELECT
con.contest_id, con.hacker_id, con.name, col.college_id, cal.challenge_id, total_submissions, total_accepted_submissions FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges cal ON col.college_id = cal.college_id LEFT JOIN SUM_SUB ss ON cal.challenge_id = ss.challenge_id ) SELECT con_sub.contest_id, con_sub.hacker_id, con_sub.name, SUM(COALESCE(con_sub.total_submissions, 0)), SUM(COALESCE(con_sub.total_accepted_submissions, 0)), SUM(COALESCE(con_view.total_views, 0)), SUM(COALESCE(con_view.total_unique_views, 0)) FROM con_sub FULL JOIN con_view ON con_sub.contest_id = con_view.contest_id GROUP BY con_sub.contest_id, con_sub.hacker_id, con_sub.name HAVING SUM(COALESCE(con_sub.total_submissions, 0)) + SUM(COALESCE(con_sub.total_accepted_submissions, 0)) + SUM(COALESCE(con_view.total_views, 0)) + SUM(COALESCE(con_view.total_unique_views, 0)) > 0 ORDER BY con_sub.contest_id;

I really didn’t like this question. I wasted so much time trying to validate my query, thinking it was wrong, only to find out it was fine. The real issue was the duplicates in the stats tables (view_stats and submission_stats). It doesn’t make sense why there are duplicates. They should’ve mentioned that in the question. This is a SQL test, not an English test!

my answer - MySQL:

SELECT c.contest_id, c.hacker_id, c.name, sum(coalesce(total_submissions,0)) as total_submissions, sum(coalesce(total_accepted_submissions,0)) as total_accepted_submissions, sum(coalesce(total_views,0)) as total_views, sum(coalesce(total_unique_views,0)) as total_unique_views from Contests as c left join Colleges as col on col.contest_id = c.contest_id left join Challenges as chl on chl.college_id = col.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 ) as vs on vs.challenge_id = chl.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
) as ss on ss.challenge_id = chl. challenge_id group by c.contest_id, c.hacker_id, c.name having not (total_submissions =0 and total_accepted_submissions = 0 and total_views =0 and total_unique_views=0 ) order by c.contest_id;

Horrible question.

Logically view_stats rows > submissions_stats rows , as people will view before submit, it makes no sense that some rows in submission_stats is not present in view_stats.

And because of this instead of (contests inner join college inner join challenges ) left join (view_stats left join submission_stats on vs.challenge_id = ss.challenge_id) ,

you'll need to (contests inner join college inner join challenges) left join (view_stats) on contest.challenge_id = vs.challenge_id left join (submission_stats) contest.challenge_id = ss.challenge_id

select * from (select contest_id, hacker_id, name, coalesce(sum(total_submissions),0) as ts, coalesce(sum(total_accepted_submissions),0) as tas, coalesce(sum(total_views),0) as tv, coalesce(sum(total_unique_views),0) as tuv from ((select cts.contest_id, hacker_id, name, chln.challenge_id from contests cts join colleges clg on cts.contest_id = clg.contest_id join challenges chln on clg.college_id = chln.college_id)sqa 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)sqb on sqa.challenge_id = sqb.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)sqc on sqa.challenge_id = sqc.challenge_id) group by contest_id, hacker_id, name)sq1 where ts <> 0 or tas <> 0 or tv <> 0 or tuv <> 0 order by contest_id;

Easy to understand code for MySQL and ORACLE

Here the mysql using its old server, you will recieve Styntax error

MySQL table creation code :

-- Drop existing tables if they exist
DROP TABLE IF EXISTS Contests;
DROP TABLE IF EXISTS Colleges;
DROP TABLE IF EXISTS Challenges;
DROP TABLE IF EXISTS View_Stats;
DROP TABLE IF EXISTS Submission_Stats;

-- Create Contests table
CREATE TABLE Contests (
    contest_id INT PRIMARY KEY,
    hacker_id INT,
    name VARCHAR(100)
);

-- Create Colleges table
CREATE TABLE Colleges (
    college_id INT PRIMARY KEY,
    contest_id INT,
    FOREIGN KEY (contest_id) REFERENCES Contests(contest_id)
);

-- Create Challenges table
CREATE TABLE Challenges (
    challenge_id INT PRIMARY KEY,
    college_id INT,
    FOREIGN KEY (college_id) REFERENCES Colleges(college_id)
);

-- Create View_Stats table
CREATE TABLE View_Stats (
    challenge_id INT,
    total_views INT,
    total_unique_views INT,
    FOREIGN KEY (challenge_id) REFERENCES Challenges(challenge_id)
);

-- Create Submission_Stats table
CREATE TABLE Submission_Stats (
    challenge_id INT,
    total_submissions INT,
    total_accepted_submissions INT,
    FOREIGN KEY (challenge_id) REFERENCES Challenges(challenge_id)
);

-- Insert sample data into Contests
INSERT INTO Contests (contest_id, hacker_id, name) VALUES
(66406, 17973, 'Rose'),
(66556, 79153, 'Angela'),
(94828, 80275, 'Frank');

-- Insert sample data into Colleges
INSERT INTO Colleges (college_id, contest_id) VALUES
(11219, 66406),
(32473, 66556),
(56685, 94828);

-- Insert sample data into Challenges
INSERT INTO Challenges (challenge_id, college_id) VALUES
(18765, 11219),
(47127, 11219),
(60292, 32473),
(72974, 56685);

-- Insert sample data into View_Stats
INSERT INTO View_Stats (challenge_id, total_views, total_unique_views) VALUES
(47127, 26, 19),
(47127, 15, 14),
(18765, 43, 10),
(18765, 72, 13),
(60292, 11, 10),
(72974, 41, 15);

-- Insert sample data into Submission_Stats
INSERT INTO Submission_Stats (challenge_id, total_submissions, total_accepted_submissions) VALUES
(47127, 27, 10),
(47127, 56, 18),
(72974, 68, 24),
(72974, 82, 14);

Note : Please run in oracle server

• Feel free to ask if you have any doubt.

with main_cte as(
    select t1.challenge_id,t2.college_id,t2.contest_id
    from Challenges t1 left join Colleges t2
    on t1.college_id = t2.college_id
),
view_stats_gb as(
    select t2.contest_id, sum(t1.total_views) as total_views,
    sum(t1.total_unique_views) as total_unique_views
    from View_Stats t1 join
    main_cte t2 on t1.challenge_id = t2.challenge_id
    group by t2.contest_id
),
Submission_Stats_gb as (
    select t2.contest_id, sum(t1.total_submissions) as total_submissions,
    sum(t1.total_accepted_submissions) as total_accepted_submissions
    from Submission_Stats  t1 join
    main_cte t2 on t1.challenge_id = t2.challenge_id
    group by t2.contest_id
)
select t1.*,
t3.total_submissions,
t3.total_accepted_submissions,
t2.total_views,
t2.total_unique_views
from Contests t1 
join view_stats_gb t2 on  t1.contest_id = t2.contest_id
join Submission_Stats_gb t3 on t1.contest_id = t3.contest_id
where 
(t3.total_submissions +
    t3.total_accepted_submissions +
    t2.total_views +
    t2.total_unique_views) <> 0
order by t1.contest_id;