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.
What's wrong with the code below (MySQL)? The IDE keeps throwing an error
WITH summed_views AS (
SELECT
challenge_id,
SUM(total_views) AS total_views,
SUM(total_unique_views) AS total_unique_views
FROM view_stats
GROUP BY challenge_id
),
summed_submissions AS (
SELECT
challenge_id,
SUM(total_submissions) AS total_submissions,
SUM(total_accepted_submissions) AS total_accepted_submissions
FROM submission_stats
GROUP BY challenge_id
),
final_table AS (
SELECT
con.contest_id,
con.hacker_id,
con.name,
COALESCE(SUM(total_submissions),0) AS total_submissions,
COALESCE(SUM(total_accepted_submissions),0) as total_accepted_submissions,
COALESCE(SUM(total_views),0) as total_views,
COALESCE(SUM(total_unique_views),0) as total_unique_views
FROM
challenges as chall
LEFT JOIN
summed_views AS SV
ON chall.challenge_id = SV.challenge_id
LEFT JOIN
summed_submissions AS SS
ON
SS.challenge_id = chall.challenge_id
JOIN
colleges AS coll
ON
coll.college_id = chall.college_id
JOIN
contests AS con
ON
con.contest_id = coll.contest_id
GROUP BY
con.contest_id, con.hacker_id, con.name
)
SELECT
*
FROM final_table
WHERE total_submissions > 0 or total_accepted_submissions > 0 or total_views > 0 or total_unique_views > 0
ORDER BY final_table.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 →
What's wrong with the code below (MySQL)? The IDE keeps throwing an error
WITH summed_views AS ( SELECT challenge_id, SUM(total_views) AS total_views, SUM(total_unique_views) AS total_unique_views FROM view_stats GROUP BY challenge_id ),
summed_submissions AS ( SELECT challenge_id, SUM(total_submissions) AS total_submissions, SUM(total_accepted_submissions) AS total_accepted_submissions FROM submission_stats GROUP BY challenge_id ),
final_table AS ( SELECT con.contest_id, con.hacker_id, con.name, COALESCE(SUM(total_submissions),0) AS total_submissions, COALESCE(SUM(total_accepted_submissions),0) as total_accepted_submissions, COALESCE(SUM(total_views),0) as total_views, COALESCE(SUM(total_unique_views),0) as total_unique_views FROM challenges as chall LEFT JOIN summed_views AS SV ON chall.challenge_id = SV.challenge_id LEFT JOIN summed_submissions AS SS ON SS.challenge_id = chall.challenge_id JOIN colleges AS coll ON coll.college_id = chall.college_id JOIN contests AS con ON con.contest_id = coll.contest_id GROUP BY con.contest_id, con.hacker_id, con.name
)
SELECT * FROM final_table WHERE total_submissions > 0 or total_accepted_submissions > 0 or total_views > 0 or total_unique_views > 0 ORDER BY final_table.contest_id;