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.
WITH cte1 as (
SELECT
c.contest_id
, c.hacker_id
, c.name
, SUM(ss.total_submissions) as sum_total_submissions
, SUM(ss.total_accepted_submissions) as sum_total_accepted_submissions
FROM Contests c
JOIN Colleges c2 ON c.contest_id = c2.contest_id
JOIN Challenges c3 ON c2.college_id = c3.college_id
FULL OUTER JOIN Submission_Stats ss ON c3.challenge_id = ss.challenge_id
WHERE c.contest_id is not null
GROUP BY c.contest_id
, c.hacker_id
, c.name
),
cte2 as (
SELECT
c.contest_id
, c.hacker_id
, c.name
, SUM(vs.total_views) as sum_total_views
, SUM(vs.total_unique_views) sum_total_unique_views
FROM Contests c
JOIN Colleges c2 ON c.contest_id = c2.contest_id
JOIN Challenges c3 ON c2.college_id = c3.college_id
JOIN View_Stats vs ON c3.challenge_id = vs.challenge_id
GROUP BY c.contest_id
, c.hacker_id
, c.name
)
SELECT
cte1.contest_id
, cte1.hacker_id
, cte1.name
, ISNULL(sum_total_submissions, 0)
, ISNULL(sum_total_accepted_submissions, 0)
, ISNULL(sum_total_views, 0)
, ISNULL(sum_total_unique_views, 0)
FROM cte1 FULL OUTER JOIN cte2 ON cte1.contest_id = cte2.contest_id
WHERE sum_total_submissions > 0
OR sum_total_accepted_submissions > 0
OR sum_total_views > 0
OR sum_total_unique_views > 0
ORDER BY cte1.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 →
WITH cte1 as ( SELECT c.contest_id , c.hacker_id , c.name , SUM(ss.total_submissions) as sum_total_submissions , SUM(ss.total_accepted_submissions) as sum_total_accepted_submissions FROM Contests c JOIN Colleges c2 ON c.contest_id = c2.contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id FULL OUTER JOIN Submission_Stats ss ON c3.challenge_id = ss.challenge_id WHERE c.contest_id is not null GROUP BY c.contest_id , c.hacker_id , c.name ), cte2 as ( SELECT c.contest_id , c.hacker_id , c.name , SUM(vs.total_views) as sum_total_views , SUM(vs.total_unique_views) sum_total_unique_views FROM Contests c JOIN Colleges c2 ON c.contest_id = c2.contest_id JOIN Challenges c3 ON c2.college_id = c3.college_id JOIN View_Stats vs ON c3.challenge_id = vs.challenge_id GROUP BY c.contest_id , c.hacker_id , c.name ) SELECT cte1.contest_id , cte1.hacker_id , cte1.name , ISNULL(sum_total_submissions, 0) , ISNULL(sum_total_accepted_submissions, 0) , ISNULL(sum_total_views, 0) , ISNULL(sum_total_unique_views, 0) FROM cte1 FULL OUTER JOIN cte2 ON cte1.contest_id = cte2.contest_id WHERE sum_total_submissions > 0 OR sum_total_accepted_submissions > 0 OR sum_total_views > 0 OR sum_total_unique_views > 0 ORDER BY cte1.contest_id