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,
SUM(COALESCE(SS.TOTAL_SUBMISSIONS, 0)) AS TS,
SUM(COALESCE(SS.TOTAL_ACCEPTED_SUBMISSIONS, 0)) AS TAS,
SUM(COALESCE(VS.TOTAL_VIEWS, 0)) AS TV,
SUM(COALESCE(VS.TOTAL_UNIQUE_VIEWS, 0)) AS TUV
FROM CONTESTS C
JOIN COLLEGES CO ON C.CONTEST_ID = CO.CONTEST_ID
JOIN CHALLENGES CH ON CO.COLLEGE_ID = CH.COLLEGE_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 SS.CHALLENGE_ID = CH.CHALLENGE_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 VS.CHALLENGE_ID = CH.CHALLENGE_ID
GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME
HAVING (TS + TAS + TV + TUV) > 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 →
SELECT C.CONTEST_ID, C.HACKER_ID, C.NAME, SUM(COALESCE(SS.TOTAL_SUBMISSIONS, 0)) AS TS, SUM(COALESCE(SS.TOTAL_ACCEPTED_SUBMISSIONS, 0)) AS TAS, SUM(COALESCE(VS.TOTAL_VIEWS, 0)) AS TV, SUM(COALESCE(VS.TOTAL_UNIQUE_VIEWS, 0)) AS TUV FROM CONTESTS C JOIN COLLEGES CO ON C.CONTEST_ID = CO.CONTEST_ID JOIN CHALLENGES CH ON CO.COLLEGE_ID = CH.COLLEGE_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 SS.CHALLENGE_ID = CH.CHALLENGE_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 VS.CHALLENGE_ID = CH.CHALLENGE_ID GROUP BY C.CONTEST_ID, C.HACKER_ID, C.NAME HAVING (TS + TAS + TV + TUV) > 0 ORDER BY C.CONTEST_ID;