- Prepare
- SQL
- Advanced Join
- Interviews
- Discussions
Interviews
Interviews
+ 0 comments MYSQL Solution
select c.contest_id, c.hacker_id, c.name, sum(ss.ts) as a, sum(ss.tas) as b, sum(vs.tv) as c, sum(vs.tuv) as d from contests as con inner join colleges as co on con.contest_id = co.contest_id inner join challenges as ch on ch.college_id = co.college_id left join (select challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv from view_stats group by challenge_id) as vs on ch.challenge_id = vs.challenge_id left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by challenge_id) as ss on ss.challenge_id = ch.challenge_id group by 1,2,3 having (a+b+c+d)<>0 order by 1
+ 0 comments -- MySQL Solution
SELECT * FROM ( SELECT c.contest_id, c.hacker_id, c.name,
SUM(A) A, SUM(B) B, SUM(C) C, SUM(D) D FROM Contests c LEFT JOIN Colleges co ON co.contest_id = c.contest_id LEFT JOIN Challenges ch ON ch.college_id = co.college_id LEFT JOIN ( SELECT challenge_id, SUM(v.total_views) C, SUM(v.total_unique_views) D FROM View_Stats v GROUP BY challenge_id) E ON E.challenge_id = ch.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(s.total_submissions) A, SUM(s.total_accepted_submissions) B FROM Submission_Stats s GROUP BY challenge_id) F ON F.challenge_id = ch.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name ORDER BY c.contest_id ) AS G WHERE A != 0 AND B != 0 AND C != 0 AND D != 0;
+ 0 comments My sql solution:
select c.contest_id, c.hacker_id, c.name, coalesce(s.total_Submission,0) total_Submission, coalesce(s.total_accepted_Submission,0) total_accepted_Submission, coalesce(v.total_views,0) total_views, coalesce(v.total_unique_views,0) total_unique_views from contests c left join ( select
co.contest_id, sum(ss.total_Submissions) total_Submission, sum(ss.total_accepted_Submissions) total_accepted_Submission from colleges co inner join challenges ch on ch.college_id=co.college_id inner join Submission_Stats ss on ss.challenge_id= ch.challenge_id group by co.contest_id ) s on s.contest_id=c.contest_id left join ( select co.contest_id, sum(vs.total_views) total_views, sum(vs.total_unique_views) total_unique_views from colleges co inner join challenges ch on ch.college_id=co.college_id inner join view_stats vs on vs.challenge_id= ch.challenge_id group by co.contest_id ) v on v.contest_id=c.contest_id where total_Submission+total_accepted_Submission+total_views+ total_unique_views>0 ;
+ 0 comments WITH submissions AS ( SELECT co.contest_id, SUM(ss.total_submissions) tot_sub, SUM(ss.total_accepted_submissions) tot_acc_sub FROM Colleges co JOIN Challenges ch ON co.college_id = ch.college_id JOIN Submission_Stats ss ON ch.challenge_id = ss.challenge_id GROUP BY co.contest_id ), views AS ( SELECT co.contest_id, SUM(vs.total_views) tot_views, SUM(vs.total_unique_views) tot_uni_views FROM Colleges co JOIN Challenges ch ON co.college_id = ch.college_id JOIN View_Stats vs ON ch.challenge_id = vs.challenge_id GROUP BY co.contest_id ) SELECT c.contest_id, c.hacker_id, c.name, s.tot_sub, s.tot_acc_sub, v.tot_views, v.tot_uni_views FROM Contests c JOIN submissions s ON c.contest_id = s.contest_id JOIN views v ON s.contest_id = v.contest_id
+ 0 comments SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSION) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSION) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEW) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEW) AS TOTAL_UNIQUE_VIEWS FROM CONTESTS AS A LEFT JOIN COLLEGES AS B ON A.CONTEST_ID = B.CONTEST_ID LEFT JOIN CHALLENGES AS C ON B.COLLEGE_ID = C.COLLEGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_VIEWS) AS TOTAL_VIEW, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEW FROM VIEW_STATS GROUP BY CHALLENGE_ID) AS D ON C.CHALLENGE_ID = D.CHALLENGE_ID LEFT JOIN (SELECT CHALLENGE_ID, SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSION, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSION FROM SUBMISSION_STATS GROUP BY CHALLENGE_ID) AS E ON C.CHALLENGE_ID = E.CHALLENGE_ID GROUP BY A.CONTEST_ID, A.HACKER_ID, A.NAME HAVING (TOTAL_SUBMISSIONS + TOTAL_ACCEPTED_SUBMISSIONS + TOTAL_VIEWS + TOTAL_UNIQUE_VIEWS) > 0 ORDER BY A.CONTEST_ID;
Sort 781 Discussions, By:
Please Login in order to post a comment