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,
COALESCE(SUM(COALESCE(ss.total_submissions,0)),0) AS total_submissions,
COALESCE(SUM(COALESCE(ss.total_accepted_submissions,0)),0) AS total_accepted_submissions,
COALESCE(SUM(COALESCE(vs.total_views,0)),0) AS total_views,
COALESCE(SUM(COALESCE(vs.total_unique_views,0)),0) AS total_unique_views
FROM Contests c
JOIN (
SELECT DISTINCT college_id, contest_id FROM Colleges
) uc ON c.contest_id = uc.contest_id
JOIN (
SELECT DISTINCT challenge_id, college_id FROM Challenges
) ch ON uc.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
) vs ON ch.challenge_id = vs.challenge_id
GROUP BY c.contest_id, c.hacker_id, c.name
HAVING (
SUM(COALESCE(ss.total_submissions,0)) > 0 OR
SUM(COALESCE(ss.total_accepted_submissions,0)) > 0 OR
SUM(COALESCE(vs.total_views,0)) > 0 OR
SUM(COALESCE(vs.total_unique_views,0)) > 0
)
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, COALESCE(SUM(COALESCE(ss.total_submissions,0)),0) AS total_submissions, COALESCE(SUM(COALESCE(ss.total_accepted_submissions,0)),0) AS total_accepted_submissions, COALESCE(SUM(COALESCE(vs.total_views,0)),0) AS total_views, COALESCE(SUM(COALESCE(vs.total_unique_views,0)),0) AS total_unique_views FROM Contests c JOIN ( SELECT DISTINCT college_id, contest_id FROM Colleges ) uc ON c.contest_id = uc.contest_id JOIN ( SELECT DISTINCT challenge_id, college_id FROM Challenges ) ch ON uc.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 ) vs ON ch.challenge_id = vs.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING ( SUM(COALESCE(ss.total_submissions,0)) > 0 OR SUM(COALESCE(ss.total_accepted_submissions,0)) > 0 OR SUM(COALESCE(vs.total_views,0)) > 0 OR SUM(COALESCE(vs.total_unique_views,0)) > 0 ) ORDER BY c.contest_id;