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 CTE_TotalSubmissions AS (
SELECT
challenge_id,
SUM(total_submissions) AS total_submissions_per_challenge_id,
SUM(total_accepted_submissions) AS total_accepted_submissions_per_challenge_id
FROM Submission_Stats
GROUP BY challenge_id
),
CTE_TotalViews AS (
SELECT
challenge_id,
SUM(total_views) AS total_views_per_challenge_id,
SUM(total_unique_views) AS total_unique_views_per_challenge_id
FROM View_Stats
GROUP BY challenge_id
),
CTE_TotalCollegeID AS (
SELECT
cl.contest_id,
SUM(cts.total_submissions_per_challenge_id) AS sum_submissions,
SUM(cts.total_accepted_submissions_per_challenge_id) AS sum_accepted_submissions,
SUM(ctv.total_views_per_challenge_id) AS sum_views,
SUM(ctv.total_unique_views_per_challenge_id) AS sum_unique_views
FROM Challenges c
LEFT JOIN CTE_TotalSubmissions cts ON cts.challenge_id = c.challenge_id
LEFT JOIN CTE_TotalViews ctv ON ctv.challenge_id = c.challenge_id
LEFT JOIN Colleges cl ON cl.college_id = c.college_id
GROUP BY cl.contest_id
)
SELECT
con.contest_id,
con.hacker_id,
con.name,
tot.sum_submissions,
tot.sum_accepted_submissions,
tot.sum_views,
tot.sum_unique_views
FROM Contests con
LEFT JOIN CTE_TotalCollegeID tot ON tot.contest_id = con.contest_id
WHERE
con.contest_id
i use CTE i think use it make code more readable and easy to understand
ps; sorry about my english:)
tot.sum_submissions <> 0 OR
tot.sum_accepted_submissions <> 0 OR
tot.sum_views <> 0 OR
tot.sum_unique_views <> 0
Interviews
You are viewing a single comment's thread. Return to all comments →
WITH CTE_TotalSubmissions AS ( SELECT challenge_id, SUM(total_submissions) AS total_submissions_per_challenge_id, SUM(total_accepted_submissions) AS total_accepted_submissions_per_challenge_id FROM Submission_Stats GROUP BY challenge_id ), CTE_TotalViews AS ( SELECT challenge_id, SUM(total_views) AS total_views_per_challenge_id, SUM(total_unique_views) AS total_unique_views_per_challenge_id FROM View_Stats GROUP BY challenge_id ), CTE_TotalCollegeID AS ( SELECT cl.contest_id, SUM(cts.total_submissions_per_challenge_id) AS sum_submissions, SUM(cts.total_accepted_submissions_per_challenge_id) AS sum_accepted_submissions, SUM(ctv.total_views_per_challenge_id) AS sum_views, SUM(ctv.total_unique_views_per_challenge_id) AS sum_unique_views FROM Challenges c LEFT JOIN CTE_TotalSubmissions cts ON cts.challenge_id = c.challenge_id LEFT JOIN CTE_TotalViews ctv ON ctv.challenge_id = c.challenge_id LEFT JOIN Colleges cl ON cl.college_id = c.college_id GROUP BY cl.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tot.sum_submissions, tot.sum_accepted_submissions, tot.sum_views, tot.sum_unique_views FROM Contests con LEFT JOIN CTE_TotalCollegeID tot ON tot.contest_id = con.contest_id WHERE con.contest_id i use CTE i think use it make code more readable and easy to understand ps; sorry about my english:)
ORDER BY con.contest_id;