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,
SUM(total_submissions) AS ts,
SUM(total_accepted_submissions) AS tas,
SUM(total_views) AS tv,
SUM(total_unique_views) AS tuv
FROM contests con
JOIN colleges col
ON con.contest_id = col.contest_id
JOIN challenges cha
ON col.college_id = cha.college_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 cha.challenge_id = vs.challenge_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 cha.challenge_id = ss.challenge_id
GROUP BY con.contest_id, con.hacker_id, con.name
HAVING ts + tas + tv + tuv > 0
ORDER BY 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 →
/* works in MySQL */
SELECT con.contest_id, con.hacker_id, con.name, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM contests con JOIN colleges col ON con.contest_id = col.contest_id JOIN challenges cha ON col.college_id = cha.college_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 cha.challenge_id = vs.challenge_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 cha.challenge_id = ss.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING ts + tas + tv + tuv > 0 ORDER BY contest_id;