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 SUM_TOTAL_SUBMISSIONS AS (
select
a.contest_id,
a.hacker_id,
a.name,
sum(e.total_submissions) AS total_submissions,
sum(e.total_accepted_submissions) AS total_accepted_submissions
From contests A
LEFT JOIN COLLEGES B
ON A.contest_id = B.contest_id
LEFT JOIN CHALLENGES C
ON B.COLLEGE_ID = C.COLLEGE_ID
LEFT JOIN SUBMISSION_STATS E
ON c.CHALLENGE_ID = E.CHALLENGE_ID
GROUP BY a.contest_id, a.hacker_id,
a.name
)
,SUM_TOTAL_VIEW as(
select
a.contest_id,
a.hacker_id,
sum(d.total_views) AS total_views,
sum(d.total_unique_views) AS total_unique_views
From contests A
LEFT JOIN COLLEGES B
ON A.contest_id = B.contest_id
LEFT JOIN CHALLENGES C
ON B.COLLEGE_ID = C.COLLEGE_ID
LEFT JOIN VIEW_STATS D
ON C.CHALLENGE_ID = D.CHALLENGE_ID
GROUP BY a.contest_id, a.hacker_id,
a.name
)
,DATA_FINAL AS(
SELECT A.*, B.total_views, B.total_unique_views FROM SUM_TOTAL_SUBMISSIONS A
INNER JOIN SUM_TOTAL_VIEW B
ON A.contest_id = B.contest_id AND A.hacker_id = B.hacker_id
)
SELECt * FROM DATA_FINAL
WHERE total_submissions > 0
OR total_unique_views > 0
OR total_accepted_submissions > 0
OR total_views > 0
ORDER BY contest_id
go
go
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 →
SET NOCOUNT ON;
WITH SUM_TOTAL_SUBMISSIONS AS ( select a.contest_id, a.hacker_id, a.name, sum(e.total_submissions) AS total_submissions, sum(e.total_accepted_submissions) AS total_accepted_submissions From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN SUBMISSION_STATS E ON c.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,SUM_TOTAL_VIEW as( select a.contest_id, a.hacker_id, sum(d.total_views) AS total_views, sum(d.total_unique_views) AS total_unique_views From contests A LEFT JOIN COLLEGES B ON A.contest_id = B.contest_id LEFT JOIN CHALLENGES C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN VIEW_STATS D ON C.CHALLENGE_ID = D.CHALLENGE_ID GROUP BY a.contest_id, a.hacker_id, a.name ) ,DATA_FINAL AS( SELECT A.*, B.total_views, B.total_unique_views FROM SUM_TOTAL_SUBMISSIONS A INNER JOIN SUM_TOTAL_VIEW B ON A.contest_id = B.contest_id AND A.hacker_id = B.hacker_id )
SELECt * FROM DATA_FINAL WHERE total_submissions > 0 OR total_unique_views > 0 OR total_accepted_submissions > 0 OR total_views > 0 ORDER BY contest_id
go
go