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.
- Prepare
- SQL
- Advanced Join
- Interviews
- Discussions
Interviews
Interviews
Sort by
recency
|
1376 Discussions
|
Please Login in order to post a comment
I follow the guy below me, and it worked. I break it up to be more readable:
I tried this, but it does not work. I dunno what the s. is going on :/
select c.contest_id, c.hacker_id, c.name, sum(ts),sum(tas),sum(tv),sum(tuv) from contests c inner join colleges col on c.contest_id = col.contest_id inner join challenges cha on cha.college_id = col.college_id left join (select challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv from view_stats group by 1 ) vs on vs.challenge_id = cha.challenge_id left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by 1 ) ss on ss.challenge_id = cha.challenge_id group by 1,2,3 having sum(tv)+sum(tuv)+sum(ts)+sum(tas) >0 order by contest_id
might have made it overly complicated, but it finally worked ...
WITH -- Aggregate view_stats by challenge
view_agg AS ( SELECT challenge_id, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM view_stats GROUP BY challenge_id ),
-- Aggregate submission_stats by challenge
submission_agg AS ( SELECT challenge_id, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas FROM submission_stats GROUP BY challenge_id ),
-- Join challenges to view/submission stats
challenge_data AS ( SELECT ch.college_id, ch.challenge_id, COALESCE(sa.ts, 0) AS ts, COALESCE(sa.tas, 0) AS tas, COALESCE(va.tv, 0) AS tv, COALESCE(va.tuv, 0) AS tuv FROM challenges ch LEFT JOIN submission_agg sa ON ch.challenge_id = sa.challenge_id LEFT JOIN view_agg va ON ch.challenge_id = va.challenge_id ),
-- Aggregate all stats by college
college_totals AS ( SELECT college_id, SUM(ts) AS cts, SUM(tas) AS ctas, SUM(tv) AS ctv, SUM(tuv) AS ctuv FROM challenge_data GROUP BY college_id ),
-- Join college_totals to contests and colleges
all_data AS ( SELECT con.hacker_id, con.name, col.contest_id, colt.college_id, colt.cts, colt.ctas, colt.ctv, colt.ctuv FROM college_totals colt JOIN colleges col on colt.college_id = col.college_id JOIN contests con on col.contest_id = con.contest_id),
-- Aggregate all stats by contest
contest_totals AS ( SELECT contest_id, hacker_id, name, SUM(cts) AS total_submissions, SUM(ctas) AS total_accepted_submissions, SUM(ctv) AS total_views, SUM(ctuv) AS total_unique_views FROM all_data GROUP BY contest_id, hacker_id, name )
-- Final filter and select
SELECT contest_id, hacker_id, name, total_submissions, total_accepted_submissions, total_views, total_unique_views FROM contest_totals WHERE (total_submissions + total_accepted_submissions + total_views + total_unique_views) > 0 ORDER BY contest_id;
WITH tb1 AS ( SELECT col.contest_id, SUM(ss.total_submissions) AS ts, SUM(ss.total_accepted_submissions) AS tas FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY col.contest_id ), tb2 AS ( SELECT col.contest_id, SUM(vs.total_views) AS tv, SUM(vs.total_unique_views) AS tuv FROM Colleges col JOIN Challenges ch ON col.college_id = ch.college_id JOIN View_Stats vs ON ch.challenge_id = vs.challenge_id GROUP BY col.contest_id ) SELECT con.contest_id, con.hacker_id, con.name, tb1.ts, tb1.tas, tb2.tv, tb2.tuv FROM Contests con JOIN tb1 ON con.contest_id = tb1.contest_id JOIN tb2 ON con.contest_id = tb2.contest_id WHERE tb1.ts > 0 AND tb1.tas > 0 AND tb2.tv > 0 AND tb2.tuv > 0 ORDER BY con.contest_id;