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 c1.contest_id, c1.hacker_id, c1.name, SUM(s.total_submissions) AS TotalSubmission, SUM(s.total_accepted_submissions) AS TotalAcceptedSubmission, SUM(v.total_views) AS TotalViews, SUM(v.total_unique_views) AS TotalUniqueViews
FROM Contests c1
JOIN Colleges c2 ON c1.contest_id = c2. contest_id
JOIN Challenges c3 ON c2.college_id = c3.college_id
LEFT JOIN (
SELECT c3.challenge_id, SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions,
SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions
FROM
Challenges c3
LEFT JOIN
Submission_Stats ss ON ss.challenge_id = c3.challenge_id
GROUP BY
c3.challenge_id
) AS s ON c3.challenge_id = s.challenge_id
LEFT JOIN (
SELECT c3.challenge_id, SUM(COALESCE(vs.total_views, 0)) AS total_views,
SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views
FROM
Challenges c3
LEFT JOIN
View_Stats vs ON vs.challenge_id = c3.challenge_id
GROUP BY
c3.challenge_id
) AS v ON c3.challenge_id = v.challenge_id
GROUP BY c1.contest_id, c1.hacker_id, c1.name HAVING SUM(s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) > 0
ORDER BY c1.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 c1.contest_id, c1.hacker_id, c1.name, SUM(s.total_submissions) AS TotalSubmission, SUM(s.total_accepted_submissions) AS TotalAcceptedSubmission, SUM(v.total_views) AS TotalViews, SUM(v.total_unique_views) AS TotalUniqueViews FROM Contests c1 JOIN Colleges c2 ON c1.contest_id = c2. contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id LEFT JOIN ( SELECT c3.challenge_id, SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions, SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions FROM Challenges c3 LEFT JOIN Submission_Stats ss ON ss.challenge_id = c3.challenge_id GROUP BY c3.challenge_id ) AS s ON c3.challenge_id = s.challenge_id LEFT JOIN ( SELECT c3.challenge_id, SUM(COALESCE(vs.total_views, 0)) AS total_views, SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views FROM Challenges c3 LEFT JOIN View_Stats vs ON vs.challenge_id = c3.challenge_id GROUP BY c3.challenge_id ) AS v ON c3.challenge_id = v.challenge_id GROUP BY c1.contest_id, c1.hacker_id, c1.name HAVING SUM(s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) > 0 ORDER BY c1.contest_id;