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)), SUM(COALESCE(total_accepted_submissions,0))
, SUM(COALESCE(total_views,0)), SUM(COALESCE(total_unique_views,0))
from Contests
JOIN (Select distinct college_id, contest_id from Colleges) colg on Contests.contest_id = colg.contest_id
JOIN (Select distinct college_id, challenge_id from Challenges) 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) 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) 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 →
select Contests.contest_id, hacker_id, name , SUM(COALESCE(total_submissions,0)), SUM(COALESCE(total_accepted_submissions,0)) , SUM(COALESCE(total_views,0)), SUM(COALESCE(total_unique_views,0)) from Contests JOIN (Select distinct college_id, contest_id from Colleges) colg on Contests.contest_id = colg.contest_id JOIN (Select distinct college_id, challenge_id from Challenges) 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) 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) 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;