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.
SELECT
ct.contest_id,
ct.hacker_id,
ct.Name,
SUM(ss.total_submissions) as sum_submissions,
SUM(ss.total_accepted_submissions) as sum_accepted_submissions,
SUM(vs.total_views) as sum_views,
SUM(vs.total_unique_views) as sum_unique_views
FROM
Contests ct
JOIN Colleges clg ON ct.contest_id=clg.contest_id
JOIN Challenges cs ON clg.college_id=cs.college_id
LEFT JOIN
(SELECT
challenge_id,
sum(total_views)as total_views,
sum(total_unique_views)as total_unique_views
FROM View_Stats
GROUP BY challenge_id)vs ON cs.challenge_id = vs.challenge_id
LEFT JOIN
(SELECT
challenge_id,
sum(total_submissions)as total_submissions,
sum(total_accepted_submissions)as total_accepted_submissions
FROM Submission_Stats
GROUP BY challenge_id)ss ON cs.challenge_id = ss.challenge_id
GROUP BY ct.contest_id, ct.hacker_id, ct.Name
HAVING sum_views >0 or sum_unique_views >0 or sum_submissions >0 or sum_accepted_submissions>0
ORDER BY ct.contest_id
Interviews
You are viewing a single comment's thread. Return to all comments →
SELECT ct.contest_id, ct.hacker_id, ct.Name, SUM(ss.total_submissions) as sum_submissions, SUM(ss.total_accepted_submissions) as sum_accepted_submissions, SUM(vs.total_views) as sum_views, SUM(vs.total_unique_views) as sum_unique_views FROM
Contests ct JOIN Colleges clg ON ct.contest_id=clg.contest_id JOIN Challenges cs ON clg.college_id=cs.college_id LEFT JOIN (SELECT challenge_id, sum(total_views)as total_views, sum(total_unique_views)as total_unique_views FROM View_Stats GROUP BY challenge_id)vs ON cs.challenge_id = vs.challenge_id LEFT JOIN (SELECT challenge_id, sum(total_submissions)as total_submissions, sum(total_accepted_submissions)as total_accepted_submissions FROM Submission_Stats GROUP BY challenge_id)ss ON cs.challenge_id = ss.challenge_id GROUP BY ct.contest_id, ct.hacker_id, ct.Name HAVING sum_views >0 or sum_unique_views >0 or sum_submissions >0 or sum_accepted_submissions>0 ORDER BY ct.contest_id