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.
I'm not sure why this logic won't work. More generally, this looks like an edge case testing interview question (e.g. for nulls etc) and don't see much value with
WITH contest_stats AS (
-- First aggregate all metrics at the contest level
SELECT
co.contest_id,
SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions,
SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions,
SUM(COALESCE(vs.total_views, 0)) AS total_views,
SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views
FROM colleges co
LEFT JOIN challenges ch ON ch.college_id = co.college_id
LEFT JOIN view_stats vs ON vs.challenge_id = ch.challenge_id
LEFT JOIN submission_stats ss ON ss.challenge_id = ch.challenge_id
GROUP BY c.contest_id
)
-- Join back to get contest details
SELECT
c.contest_id,
c.hacker_id,
c.name,
cs.total_submissions,
cs.total_accepted_submissions,
cs.total_views,
cs.total_unique_views
FROM contests c
JOIN contest_stats cs ON c.contest_id = cs.contest_id
WHERE (cs.total_submissions + cs.total_accepted_submissions+cs.total_views+cs.total_unique_views) > 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 →
I'm not sure why this logic won't work. More generally, this looks like an edge case testing interview question (e.g. for nulls etc) and don't see much value with
WITH contest_stats AS ( -- First aggregate all metrics at the contest level SELECT co.contest_id, SUM(COALESCE(ss.total_submissions, 0)) AS total_submissions, SUM(COALESCE(ss.total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(vs.total_views, 0)) AS total_views, SUM(COALESCE(vs.total_unique_views, 0)) AS total_unique_views FROM colleges co LEFT JOIN challenges ch ON ch.college_id = co.college_id LEFT JOIN view_stats vs ON vs.challenge_id = ch.challenge_id LEFT JOIN submission_stats ss ON ss.challenge_id = ch.challenge_id GROUP BY c.contest_id )
-- Join back to get contest details SELECT c.contest_id, c.hacker_id, c.name, cs.total_submissions, cs.total_accepted_submissions, cs.total_views, cs.total_unique_views FROM contests c JOIN contest_stats cs ON c.contest_id = cs.contest_id WHERE (cs.total_submissions + cs.total_accepted_submissions+cs.total_views+cs.total_unique_views) > 0 ORDER BY c.contest_id;