We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
The question table and table uploaded for Output is mismatched .
SELECT Contests.contest_id,hacker_id,name,
COALESCE(SUM(Submission_Stats.total_submissions),0)AS total_submissions,
COALESCE(SUM(Submission_Stats.total_accepted_submissions),0)AS total_accepted_submissions,
COALESCE(SUM(View_Stats.total_views),0)AS total_views,
COALESCE(SUM(View_Stats.total_unique_views),0)AS total_unique_views
FROM Contests
JOIN Colleges ON Contests.contest_id=Colleges.contest_id
JOIN Challenges ON Colleges.college_id=Challenges.college_id
JOIN View_Stats ON Challenges.challenge_id=View_Stats.challenge_id
JOIN Submission_Stats on Challenges.challenge_id=Submission_Stats.challenge_id
GROUP BY Contests.contest_id,Contests.hacker_id,Contests.name
HAVING COALESCE(SUM(total_submissions),0)+COALESCE(SUM(total_accepted_submissions),0)+
COALESCE(SUM(total_views),0)+COALESCE(SUM(total_unique_views),0)>0
ORDER BY Contests.contest_id
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Interviews
You are viewing a single comment's thread. Return to all comments →
The question table and table uploaded for Output is mismatched .
SELECT Contests.contest_id,hacker_id,name, COALESCE(SUM(Submission_Stats.total_submissions),0)AS total_submissions, COALESCE(SUM(Submission_Stats.total_accepted_submissions),0)AS total_accepted_submissions, COALESCE(SUM(View_Stats.total_views),0)AS total_views, COALESCE(SUM(View_Stats.total_unique_views),0)AS total_unique_views FROM Contests JOIN Colleges ON Contests.contest_id=Colleges.contest_id JOIN Challenges ON Colleges.college_id=Challenges.college_id JOIN View_Stats ON Challenges.challenge_id=View_Stats.challenge_id JOIN Submission_Stats on Challenges.challenge_id=Submission_Stats.challenge_id GROUP BY Contests.contest_id,Contests.hacker_id,Contests.name HAVING COALESCE(SUM(total_submissions),0)+COALESCE(SUM(total_accepted_submissions),0)+ COALESCE(SUM(total_views),0)+COALESCE(SUM(total_unique_views),0)>0 ORDER BY Contests.contest_id