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
c.contest_id,
c.hacker_id,
c.name,
t.total_submissions,
t.total_accepted_submissions,
t.total_views,
t.total_unique_views
FROM Contests AS c
JOIN (
SELECT
m.contest_id,
SUM(IFNULL(ss.ts, 0)) AS total_submissions,
SUM(IFNULL(ss.tas, 0)) AS total_accepted_submissions,
SUM(IFNULL(vs.tv, 0)) AS total_views,
SUM(IFNULL(vs.tuv, 0)) AS total_unique_views
FROM (
SELECT ch.challenge_id, col.contest_id
FROM Colleges AS col
JOIN Challenges AS ch
ON ch.college_id = col.college_id
) AS m
LEFT JOIN (
SELECT
challenge_id,
SUM(total_submissions) AS ts,
SUM(total_accepted_submissions) AS tas
FROM Submission_Stats
GROUP BY challenge_id
) AS ss
ON ss.challenge_id = m.challenge_id
LEFT JOIN (
SELECT
challenge_id,
SUM(total_views) AS tv,
SUM(total_unique_views) AS tuv
FROM View_Stats
GROUP BY challenge_id
) AS vs
ON vs.challenge_id = m.challenge_id
GROUP BY m.contest_id
HAVING
(total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0
) AS t
ON t.contest_id = c.contest_id
ORDER BY c.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 c.contest_id, c.hacker_id, c.name, t.total_submissions, t.total_accepted_submissions, t.total_views, t.total_unique_views FROM Contests AS c JOIN ( SELECT m.contest_id, SUM(IFNULL(ss.ts, 0)) AS total_submissions, SUM(IFNULL(ss.tas, 0)) AS total_accepted_submissions, SUM(IFNULL(vs.tv, 0)) AS total_views, SUM(IFNULL(vs.tuv, 0)) AS total_unique_views FROM ( SELECT ch.challenge_id, col.contest_id FROM Colleges AS col JOIN Challenges AS ch ON ch.college_id = col.college_id ) AS m LEFT JOIN ( SELECT challenge_id, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas FROM Submission_Stats GROUP BY challenge_id ) AS ss ON ss.challenge_id = m.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM View_Stats GROUP BY challenge_id ) AS vs ON vs.challenge_id = m.challenge_id GROUP BY m.contest_id HAVING (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 ) AS t ON t.contest_id = c.contest_id ORDER BY c.contest_id;