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
Contests.contest_id,
hacker_id,
name,
SUM(COALESCE(total_submissions, 0)) AS total_submissions,
SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions,
SUM(COALESCE(total_views, 0)) AS total_views,
SUM(COALESCE(total_unique_views, 0)) AS total_unique_views
FROM Contests
JOIN (
SELECT DISTINCT
college_id,
contest_id
FROM Colleges
) AS colg
ON Contests.contest_id = colg.contest_id
JOIN (
SELECT DISTINCT
college_id,
challenge_id
FROM Challenges
) AS chlng
ON colg.college_id = chlng.college_id
LEFT JOIN (
SELECT
challenge_id,
SUM(COALESCE(total_views, 0)) AS total_views,
SUM(COALESCE(total_unique_views, 0)) AS total_unique_views
FROM View_Stats
GROUP BY challenge_id
) AS vs
ON chlng.challenge_id = vs.challenge_id
LEFT JOIN (
SELECT
challenge_id,
SUM(COALESCE(total_submissions, 0)) AS total_submissions,
SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions
FROM Submission_Stats
GROUP BY challenge_id
) AS ss
ON chlng.challenge_id = ss.challenge_id
GROUP BY
Contests.contest_id,
hacker_id,
name
HAVING
SUM(COALESCE(total_submissions, 0)) != 0
OR SUM(COALESCE(total_accepted_submissions, 0)) != 0
OR SUM(COALESCE(total_views, 0)) != 0
OR SUM(COALESCE(total_unique_views, 0)) != 0
ORDER BY
Contests.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 →
MySQL Query:
SELECT Contests.contest_id, hacker_id, name, SUM(COALESCE(total_submissions, 0)) AS total_submissions, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM Contests JOIN ( SELECT DISTINCT college_id, contest_id FROM Colleges ) AS colg ON Contests.contest_id = colg.contest_id JOIN ( SELECT DISTINCT college_id, challenge_id FROM Challenges ) AS chlng ON colg.college_id = chlng.college_id LEFT JOIN ( SELECT challenge_id, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM View_Stats GROUP BY challenge_id ) AS vs ON chlng.challenge_id = vs.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(COALESCE(total_submissions, 0)) AS total_submissions, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id ) AS ss ON chlng.challenge_id = ss.challenge_id GROUP BY Contests.contest_id, hacker_id, name HAVING SUM(COALESCE(total_submissions, 0)) != 0 OR SUM(COALESCE(total_accepted_submissions, 0)) != 0 OR SUM(COALESCE(total_views, 0)) != 0 OR SUM(COALESCE(total_unique_views, 0)) != 0 ORDER BY Contests.contest_id;