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 temp AS (
SELECT
b.contest_id,
SUM(total_submissions) AS sum_total_submissions,
SUM(total_accepted_submissions) AS sum_total_accepted_submissions,
SUM(total_views) AS sum_total_views,
SUM(total_unique_views) AS sum_total_unique_views
FROM
colleges AS b
LEFT JOIN challenges AS c ON b.college_id = c.college_id
LEFT JOIN view_stats AS d ON c.challenge_id = d.challenge_id
LEFT JOIN submission_stats AS e ON c.challenge_id = e.challenge_id
GROUP BY
b.contest_id
)
SELECT
a.contest_id,f.hacker_id,f.name,sum_total_submissions,sum_total_accepted_submissions,
sum_total_views,sum_total_unique_views
FROM
temp as a
inner join contests as f on a.contest_id=f.contest_id
where (sum_total_submissions+
sum_total_accepted_submissions+
sum_total_views +
sum_total_unique_views)> 0
order by a.contest_id ;
Interviews
You are viewing a single comment's thread. Return to all comments →
can anyone explain, where i'm missing the logic ?
WITH temp AS ( SELECT b.contest_id, SUM(total_submissions) AS sum_total_submissions, SUM(total_accepted_submissions) AS sum_total_accepted_submissions, SUM(total_views) AS sum_total_views, SUM(total_unique_views) AS sum_total_unique_views FROM colleges AS b LEFT JOIN challenges AS c ON b.college_id = c.college_id LEFT JOIN view_stats AS d ON c.challenge_id = d.challenge_id LEFT JOIN submission_stats AS e ON c.challenge_id = e.challenge_id GROUP BY b.contest_id ) SELECT a.contest_id,f.hacker_id,f.name,sum_total_submissions,sum_total_accepted_submissions, sum_total_views,sum_total_unique_views FROM temp as a inner join contests as f on a.contest_id=f.contest_id where (sum_total_submissions+ sum_total_accepted_submissions+ sum_total_views + sum_total_unique_views)> 0 order by a.contest_id ;