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
CollChal AS /* Gives me College ID, Contest ID and Challege ID */
(
SELECT
Contest_id
,Challenge_id
FROM Colleges CO JOIN Challenges CH ON
CO.College_id = CH.College_id
),
AddViews AS /* Bring in the views columns */
(
SELECT
CC.Contest_id
,CC.Challenge_id
,VS.total_views
,VS.total_unique_views
FROM CollChal CC JOIN View_Stats VS
ON CC.Challenge_id = VS.Challenge_id
),
/*
SELECT *
FROM AddTotalViews
*/
TotalViews AS /* Summarise Total views columns */
(
SELECT
Contest_id
AddSubs AS /* Bring in the Sub columns */
(
SELECT
CC.Contest_id
,CC.Challenge_id
,SS.total_submissions
,SS.total_accepted_submissions
FROM CollChal CC JOIN Submission_Stats SS
ON CC.Challenge_id = SS.Challenge_id
),
TotalSubs AS /* Summarise Total Subs columns */
(
SELECT Contest_id
Consolidated AS
(
SELECT TS.Contest_id
,TS.total_subs
,TS.total_unique_subs
,TV.total_views
,TV.total_unique_views
FROM TotalSubs TS JOIN TotalViews TV
ON TS.Contest_id = TV.Contest_id
)
/* Bring in Contests info */
SELECT CO.Contest_id
,hacker_id
,name
,C.total_subs
,C.total_unique_subs
,C.total_views
,C.total_unique_views
FROM Consolidated C JOIN Contests CO ON
C.Contest_id = CO.Contest_id
ORDER BY CO.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 →
WITH CollChal AS /* Gives me College ID, Contest ID and Challege ID */ ( SELECT
Contest_id ,Challenge_id FROM Colleges CO JOIN Challenges CH ON CO.College_id = CH.College_id
),
AddViews AS /* Bring in the views columns */ ( SELECT
CC.Contest_id ,CC.Challenge_id ,VS.total_views ,VS.total_unique_views FROM CollChal CC JOIN View_Stats VS ON CC.Challenge_id = VS.Challenge_id ),
/* SELECT * FROM AddTotalViews */
TotalViews AS /* Summarise Total views columns */ ( SELECT Contest_id
FROM AddViews
GROUP BY Contest_id ),
AddSubs AS /* Bring in the Sub columns */ ( SELECT
CC.Contest_id ,CC.Challenge_id ,SS.total_submissions ,SS.total_accepted_submissions FROM CollChal CC JOIN Submission_Stats SS ON CC.Challenge_id = SS.Challenge_id ),
TotalSubs AS /* Summarise Total Subs columns */ ( SELECT Contest_id
FROM AddSubs
GROUP BY Contest_id
),
Consolidated AS ( SELECT TS.Contest_id ,TS.total_subs ,TS.total_unique_subs ,TV.total_views ,TV.total_unique_views FROM TotalSubs TS JOIN TotalViews TV ON TS.Contest_id = TV.Contest_id )
/* Bring in Contests info */ SELECT CO.Contest_id
,hacker_id ,name ,C.total_subs ,C.total_unique_subs ,C.total_views ,C.total_unique_views
FROM Consolidated C JOIN Contests CO ON C.Contest_id = CO.Contest_id
ORDER BY CO.Contest_id