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.
The problem here is: There are more than 2 aggreations tables (The view table and the submission table). Therefore, we cannot join all of them together at once because JOIN clause will make the value duplicate resulting in wrong calculation.
For example: in for challenge_id 145: There are 4 rows consist 145 in View_Stats table, but there are only 1 row consists 145 in Submission_Stats. When we join all of them at once, the row in submssion table will be quadrupped resulting in wrong sum for total_submissions. So we must aggregate each table separately before joinning all of them together.
WITH Views_table AS (SELECT ch.challenge_id, SUM(Total_views) Totals_views, SUM(Total_unique_views) Total_unique_views
FROM Challenges ch
JOIN View_stats vw ON ch.challenge_id = vw.challenge_id
GROUP BY ch.challenge_id),
Submissions_table AS (SELECT ch.challenge_id, SUM(Total_submissions) Total_submissions, SUM(Total_accepted_submissions) Total_accepted_submissions
FROM Challenges ch
JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id
GROUP BY ch.challenge_id)
SELECT ct.contest_id, hacker_id, name, SUM(Total_submissions), SUM(Total_accepted_submissions), SUM(Totals_views), SUM(Total_unique_views)
FROM contests ct
JOIN colleges cl ON ct.Contest_id = cl.contest_id
JOIN challenges ch ON ch.college_id = cl.college_id
LEFT JOIN Views_table v ON ch.challenge_id = v.challenge_id
LEFT JOIN Submissions_table s ON ch.challenge_id = s.challenge_id
GROUP BY ct.contest_id, hacker_id, name
ORDER BY ct.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 →
The problem here is: There are more than 2 aggreations tables (The view table and the submission table). Therefore, we cannot join all of them together at once because JOIN clause will make the value duplicate resulting in wrong calculation. For example: in for challenge_id 145: There are 4 rows consist 145 in View_Stats table, but there are only 1 row consists 145 in Submission_Stats. When we join all of them at once, the row in submssion table will be quadrupped resulting in wrong sum for total_submissions. So we must aggregate each table separately before joinning all of them together. WITH Views_table AS (SELECT ch.challenge_id, SUM(Total_views) Totals_views, SUM(Total_unique_views) Total_unique_views FROM Challenges ch JOIN View_stats vw ON ch.challenge_id = vw.challenge_id GROUP BY ch.challenge_id), Submissions_table AS (SELECT ch.challenge_id, SUM(Total_submissions) Total_submissions, SUM(Total_accepted_submissions) Total_accepted_submissions FROM Challenges ch JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY ch.challenge_id) SELECT ct.contest_id, hacker_id, name, SUM(Total_submissions), SUM(Total_accepted_submissions), SUM(Totals_views), SUM(Total_unique_views) FROM contests ct JOIN colleges cl ON ct.Contest_id = cl.contest_id JOIN challenges ch ON ch.college_id = cl.college_id LEFT JOIN Views_table v ON ch.challenge_id = v.challenge_id LEFT JOIN Submissions_table s ON ch.challenge_id = s.challenge_id GROUP BY ct.contest_id, hacker_id, name ORDER BY ct.contest_id