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
|
1137 Discussions
|
Please Login in order to post a comment
My code gives me the wrong answer, can anyone debug for me please? Thanks;
SELECT c.contest_id, c.hacker_id, c.name, SUM(s.total_submissions) AS total_submissions, SUM(s.total_accepted_submissions) AS total_accepted_submissions, SUM(v.total_views) AS total_views, SUM(v.total_unique_views) AS total_unique_views FROM Contests c INNER JOIN Colleges col USING (contest_id) INNER JOIN Challenges ch USING (college_id) LEFT JOIN View_Stats v USING (challenge_id) LEFT JOIN Submission_Stats s USING (challenge_id) GROUP BY 1, 2, 3 HAVING NOT ( SUM(s.total_submissions) = 0 AND
SUM(s.total_accepted_submissions) = 0 AND SUM(v.total_views) = 0 AND SUM(v.total_unique_views) = 0 ) ORDER BY 1;
SQL Server
SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSIONS) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEWS) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEWS) 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_VIEWS, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEWS 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_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSIONS 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;
with final_data as ( select con.contest_id ,con.hacker_id, con.name,sum(ss.total_submissions) sum_total_sub ,sum(ss.total_accepted_submissions) sum_total_acc_sub,sum(vs.total_views) sum_total_vw,sum(vs.total_unique_views) sum_uni_vw from contests con inner join colleges cl on cl.contest_id = con.contest_id inner join challenges ch on ch.college_id = cl.college_id outer apply( select sum(ss.total_submissions) total_submissions ,sum(ss.total_accepted_submissions) total_accepted_submissions from Submission_Stats ss where ss.challenge_id = ch.challenge_id group by ss.challenge_id ) ss outer apply( select sum(vs.total_views)total_views,sum(vs.total_unique_views) total_unique_views from View_Stats vs where vs.challenge_id = ch.challenge_id group by vs.challenge_id ) vs group by con.contest_id ,con.hacker_id, con.name ) select * from final_data where sum_total_sub + sum_total_acc_sub+sum_total_vw+sum_uni_vw >0 order by contest_id