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.
ngoccth_SQL SERVER:
SELECT challenge_id
, SUM (total_submissions) AS sum_sub
, SUM (total_Accepted_submissions) AS sum_ac_sub
FROM Submission_Stats
GROUP BY challenge_id
)
, table2 AS (
SELECT challenge_id
, SUM (total_views) AS sum_views
, SUM (total_unique_views) AS sum_uni_views
FROM View_Stats
GROUP BY challenge_id
)
, table_3 AS (
SELECT
CASE WHEN table1.challenge_id = table2.challenge_id THEN table1.challenge_id
WHEN table1.challenge_id IS NULL THEN table2.challenge_id
WHEN table2.challenge_id IS NULL THEN table1.challenge_id
END AS challenge_id
, sum_sub
, sum_ac_sub
, sum_views
, sum_uni_views
FROM table1
FULL JOIN table2 ON table1.challenge_id = table2.challenge_id
)
SELECT Col.contest_id, hacker_id, [name]
, SUM (sum_sub) AS sum_sub
, SUM (sum_ac_sub) AS sum_ac_sub
, SUM (sum_views) AS sum_views
, SUM (sum_uni_views) AS sum_uni_views
FROM table_3
JOIN Challenges AS cha ON table_3.challenge_id = Cha.challenge_id
JOIN Colleges AS Col ON Cha.college_id = Col.college_id
JOIN Contests AS COn ON Con.contest_id = Col.contest_id
GROUP BY Col.contest_id, hacker_id, [name]
HAVING SUM (sum_sub) IS NOT NULL
AND SUM (sum_ac_sub) IS NOT NULL
AND SUM (sum_views) IS NOT NULL
AND SUM (sum_uni_views) IS NOT NULL
ORDER BY Col.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 →
ngoccth_SQL SERVER: SELECT challenge_id , SUM (total_submissions) AS sum_sub , SUM (total_Accepted_submissions) AS sum_ac_sub FROM Submission_Stats GROUP BY challenge_id ) , table2 AS ( SELECT challenge_id , SUM (total_views) AS sum_views , SUM (total_unique_views) AS sum_uni_views FROM View_Stats GROUP BY challenge_id ) , table_3 AS ( SELECT CASE WHEN table1.challenge_id = table2.challenge_id THEN table1.challenge_id WHEN table1.challenge_id IS NULL THEN table2.challenge_id WHEN table2.challenge_id IS NULL THEN table1.challenge_id END AS challenge_id , sum_sub , sum_ac_sub , sum_views , sum_uni_views FROM table1 FULL JOIN table2 ON table1.challenge_id = table2.challenge_id ) SELECT Col.contest_id, hacker_id, [name] , SUM (sum_sub) AS sum_sub , SUM (sum_ac_sub) AS sum_ac_sub , SUM (sum_views) AS sum_views , SUM (sum_uni_views) AS sum_uni_views FROM table_3 JOIN Challenges AS cha ON table_3.challenge_id = Cha.challenge_id JOIN Colleges AS Col ON Cha.college_id = Col.college_id JOIN Contests AS COn ON Con.contest_id = Col.contest_id GROUP BY Col.contest_id, hacker_id, [name] HAVING SUM (sum_sub) IS NOT NULL AND SUM (sum_ac_sub) IS NOT NULL AND SUM (sum_views) IS NOT NULL AND SUM (sum_uni_views) IS NOT NULL ORDER BY Col.contest_id