You are viewing a single comment's thread. Return to all comments →
Oracle
SELECT Contests.contest_id, Contests.hacker_id, Contests.NAME, COALESCE(SUM(Submission_Stats.total_submissions), 0), COALESCE(SUM(Submission_Stats.total_accepted_submissions), 0), COALESCE(SUM(View_Stats.total_views), 0), COALESCE(SUM(View_Stats.total_unique_views), 0) FROM Contests, Colleges, Challenges 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 ) View_Stats ON Challenges.challenge_id = View_Stats.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 ) Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id WHERE Contests.contest_id = Colleges.contest_id AND Colleges.college_id = Challenges.college_id GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME ORDER BY Contests.contest_id;
MySQL
SELECT Contests.contest_id, Contests.hacker_id, Contests.NAME, IFNULL(SUM(Submission_Stats.total_submissions), 0), IFNULL(SUM(Submission_Stats.total_accepted_submissions), 0), IFNULL(SUM(View_Stats.total_views), 0), IFNULL(SUM(View_Stats.total_unique_views), 0) FROM Contests, Colleges, Challenges 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 View_Stats ON Challenges.challenge_id = View_Stats.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 Submission_Stats ON Challenges.challenge_id = Submission_Stats.challenge_id WHERE Contests.contest_id = Colleges.contest_id AND Colleges.college_id = Challenges.college_id GROUP BY Contests.contest_id,Contests.hacker_id,Contests.NAME ORDER BY Contests.contest_id
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 →
Oracle
MySQL