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.
can anyone fix my query please mssqlserver :(
WITH SUM_SUB AS (
SELECT
challenge_id,
SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions,
SUM(COALESCE(total_submissions, 0)) AS total_submissions
FROM Submission_Stats
GROUP BY challenge_id
),
SUM_VIEW AS (
SELECT
challenge_id,
SUM(COALESCE(total_views, 0)) AS total_views,
SUM(COALESCE(total_unique_views, 0)) AS total_unique_views
FROM View_Stats
GROUP BY challenge_id
),
CON_VIEW AS (
SELECT
con.contest_id,
con.hacker_id,
con.name,
col.college_id,
cal.challenge_id,
total_views,
total_unique_views
FROM Contests con
JOIN Colleges col ON con.contest_id = col.contest_id
JOIN Challenges cal ON col.college_id = cal.college_id
JOIN SUM_VIEW ss ON cal.challenge_id = ss.challenge_id
),
CON_SUB AS (
SELECT
con.contest_id,
con.hacker_id,
con.name,
col.college_id,
cal.challenge_id,
total_submissions,
total_accepted_submissions
FROM Contests con
JOIN Colleges col ON con.contest_id = col.contest_id
JOIN Challenges cal ON col.college_id = cal.college_id
LEFT JOIN SUM_SUB ss ON cal.challenge_id = ss.challenge_id
)
SELECT
con_sub.contest_id,
con_sub.hacker_id,
con_sub.name,
SUM(COALESCE(con_sub.total_submissions, 0)),
SUM(COALESCE(con_sub.total_accepted_submissions, 0)),
SUM(COALESCE(con_view.total_views, 0)),
SUM(COALESCE(con_view.total_unique_views, 0))
FROM con_sub
FULL JOIN con_view ON con_sub.contest_id = con_view.contest_id
GROUP BY
con_sub.contest_id,
con_sub.hacker_id,
con_sub.name
HAVING
SUM(COALESCE(con_sub.total_submissions, 0)) +
SUM(COALESCE(con_sub.total_accepted_submissions, 0)) +
SUM(COALESCE(con_view.total_views, 0)) +
SUM(COALESCE(con_view.total_unique_views, 0)) > 0
ORDER BY con_sub.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 →
can anyone fix my query please mssqlserver :( WITH SUM_SUB AS ( SELECT challenge_id, SUM(COALESCE(total_accepted_submissions, 0)) AS total_accepted_submissions, SUM(COALESCE(total_submissions, 0)) AS total_submissions FROM Submission_Stats GROUP BY challenge_id ), SUM_VIEW AS ( SELECT challenge_id, SUM(COALESCE(total_views, 0)) AS total_views, SUM(COALESCE(total_unique_views, 0)) AS total_unique_views FROM View_Stats GROUP BY challenge_id ), CON_VIEW AS ( SELECT
con.contest_id, con.hacker_id, con.name, col.college_id, cal.challenge_id, total_views, total_unique_views FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges cal ON col.college_id = cal.college_id
JOIN SUM_VIEW ss ON cal.challenge_id = ss.challenge_id ), CON_SUB AS ( SELECT
con.contest_id, con.hacker_id, con.name, col.college_id, cal.challenge_id, total_submissions, total_accepted_submissions FROM Contests con JOIN Colleges col ON con.contest_id = col.contest_id JOIN Challenges cal ON col.college_id = cal.college_id LEFT JOIN SUM_SUB ss ON cal.challenge_id = ss.challenge_id ) SELECT con_sub.contest_id, con_sub.hacker_id, con_sub.name, SUM(COALESCE(con_sub.total_submissions, 0)), SUM(COALESCE(con_sub.total_accepted_submissions, 0)), SUM(COALESCE(con_view.total_views, 0)), SUM(COALESCE(con_view.total_unique_views, 0)) FROM con_sub FULL JOIN con_view ON con_sub.contest_id = con_view.contest_id GROUP BY con_sub.contest_id, con_sub.hacker_id, con_sub.name HAVING SUM(COALESCE(con_sub.total_submissions, 0)) + SUM(COALESCE(con_sub.total_accepted_submissions, 0)) + SUM(COALESCE(con_view.total_views, 0)) + SUM(COALESCE(con_view.total_unique_views, 0)) > 0 ORDER BY con_sub.contest_id;