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 c.contest_id, c.hacker_id,c.name , s.ts, s.tas, v.tv, v.tuv
from contests c
inner join
(SELECT c.contest_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas FROM contests c
INNER JOIN colleges cl ON cl.contest_id=c.contest_id
INNER JOIN challenges ch ON cl.college_id=ch.college_id
INNER JOIN submission_stats ss ON ss.challenge_id=ch.challenge_id
GROUP BY c.contest_id
having SUM(total_submissions)+SUM(total_accepted_submissions) > 0) s
ON c.contest_id=s.contest_id
INNER JOIN
(
SELECT c.contest_id, sum(total_views) as tv, sum(total_unique_views) as tuv FROM contests c
INNER JOIN colleges cl ON cl.contest_id=c.contest_id
INNER JOIN challenges ch ON cl.college_id=ch.college_id
INNER JOIN view_stats vs ON vs.challenge_id=ch.challenge_id
GROUP BY c.contest_id
having SUM(total_views)+SUM(total_unique_views) > 0
) v
ON v.contest_id=s.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 c.contest_id, c.hacker_id,c.name , s.ts, s.tas, v.tv, v.tuv from contests c inner join (SELECT c.contest_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas FROM contests c INNER JOIN colleges cl ON cl.contest_id=c.contest_id INNER JOIN challenges ch ON cl.college_id=ch.college_id INNER JOIN submission_stats ss ON ss.challenge_id=ch.challenge_id GROUP BY c.contest_id having SUM(total_submissions)+SUM(total_accepted_submissions) > 0) s ON c.contest_id=s.contest_id INNER JOIN ( SELECT c.contest_id, sum(total_views) as tv, sum(total_unique_views) as tuv FROM contests c INNER JOIN colleges cl ON cl.contest_id=c.contest_id INNER JOIN challenges ch ON cl.college_id=ch.college_id INNER JOIN view_stats vs ON vs.challenge_id=ch.challenge_id GROUP BY c.contest_id having SUM(total_views)+SUM(total_unique_views) > 0 ) v ON v.contest_id=s.contest_id;