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.
SELECT
con.contest_id,
con.hacker_id,
con.name,
COALESCE(SUM(sg.total_submissions), 0) AS total_submissions,
COALESCE(SUM(sg.total_accepted_submissions), 0) AS total_accepted_submissions,
COALESCE(SUM(vg.total_views), 0) AS total_views,
COALESCE(SUM(vg.total_unique_views), 0) AS 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
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) sg ON cha.challenge_id = sg.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) vg ON cha.challenge_id = vg.challenge_id
GROUP BY
con.contest_id,
con.hacker_id,
con.name
HAVING
COALESCE(SUM(sg.total_submissions), 0) != 0 OR
COALESCE(SUM(sg.total_accepted_submissions), 0) != 0 OR
COALESCE(SUM(vg.total_views), 0) != 0 OR
COALESCE(SUM(vg.total_unique_views), 0) != 0
ORDER BY
con.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 →
SELECT con.contest_id, con.hacker_id, con.name, COALESCE(SUM(sg.total_submissions), 0) AS total_submissions, COALESCE(SUM(sg.total_accepted_submissions), 0) AS total_accepted_submissions, COALESCE(SUM(vg.total_views), 0) AS total_views, COALESCE(SUM(vg.total_unique_views), 0) AS 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 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) sg ON cha.challenge_id = sg.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) vg ON cha.challenge_id = vg.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING COALESCE(SUM(sg.total_submissions), 0) != 0 OR COALESCE(SUM(sg.total_accepted_submissions), 0) != 0 OR COALESCE(SUM(vg.total_views), 0) != 0 OR COALESCE(SUM(vg.total_unique_views), 0) != 0 ORDER BY con.contest_id;