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 A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSION) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSION) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEW) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEW) AS TOTAL_UNIQUE_VIEWS
FROM CONTESTS AS A
LEFT JOIN COLLEGES AS B ON A.CONTEST_ID = B.CONTEST_ID
LEFT JOIN CHALLENGES AS C ON B.COLLEGE_ID = C.COLLEGE_ID
LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_VIEWS) AS TOTAL_VIEW, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEW
FROM VIEW_STATS
GROUP BY CHALLENGE_ID) AS D
ON C.CHALLENGE_ID = D.CHALLENGE_ID
LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSION, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSION
FROM SUBMISSION_STATS
GROUP BY CHALLENGE_ID) AS E
ON C.CHALLENGE_ID = E.CHALLENGE_ID
GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME
HAVING (TOTAL_SUBMISSIONS + TOTAL_ACCEPTED_SUBMISSIONS + TOTAL_VIEWS + TOTAL_UNIQUE_VIEWS) > 0 ORDER BY A.CONTEST_ID;
Interviews
You are viewing a single comment's thread. Return to all comments →
SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSION) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSION) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEW) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEW) AS TOTAL_UNIQUE_VIEWS FROM CONTESTS AS A LEFT JOIN COLLEGES AS B ON A.CONTEST_ID = B.CONTEST_ID LEFT JOIN CHALLENGES AS C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_VIEWS) AS TOTAL_VIEW, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEW FROM VIEW_STATS GROUP BY CHALLENGE_ID) AS D ON C.CHALLENGE_ID = D.CHALLENGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSION, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSION FROM SUBMISSION_STATS GROUP BY CHALLENGE_ID) AS E ON C.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME HAVING (TOTAL_SUBMISSIONS + TOTAL_ACCEPTED_SUBMISSIONS + TOTAL_VIEWS + TOTAL_UNIQUE_VIEWS) > 0 ORDER BY A.CONTEST_ID;