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
|
1284 Discussions
|
Please Login in order to post a comment
ngoccth_SQL SERVER: SELECT challenge_id , SUM (total_submissions) AS sum_sub , SUM (total_Accepted_submissions) AS sum_ac_sub FROM Submission_Stats GROUP BY challenge_id ) , table2 AS ( SELECT challenge_id , SUM (total_views) AS sum_views , SUM (total_unique_views) AS sum_uni_views FROM View_Stats GROUP BY challenge_id ) , table_3 AS ( SELECT CASE WHEN table1.challenge_id = table2.challenge_id THEN table1.challenge_id WHEN table1.challenge_id IS NULL THEN table2.challenge_id WHEN table2.challenge_id IS NULL THEN table1.challenge_id END AS challenge_id , sum_sub , sum_ac_sub , sum_views , sum_uni_views FROM table1 FULL JOIN table2 ON table1.challenge_id = table2.challenge_id ) SELECT Col.contest_id, hacker_id, [name] , SUM (sum_sub) AS sum_sub , SUM (sum_ac_sub) AS sum_ac_sub , SUM (sum_views) AS sum_views , SUM (sum_uni_views) AS sum_uni_views FROM table_3 JOIN Challenges AS cha ON table_3.challenge_id = Cha.challenge_id JOIN Colleges AS Col ON Cha.college_id = Col.college_id JOIN Contests AS COn ON Con.contest_id = Col.contest_id GROUP BY Col.contest_id, hacker_id, [name] HAVING SUM (sum_sub) IS NOT NULL AND SUM (sum_ac_sub) IS NOT NULL AND SUM (sum_views) IS NOT NULL AND SUM (sum_uni_views) IS NOT NULL ORDER BY Col.contest_id
Anyone help on this what is issue in this ORACLE Query ,i am not getting proper answer SELECT con.contest_id, con.hacker_id, con.name, SUM(view2.total_submissions) AS total_submissions, SUM(view2.total_accepted_submissions) AS total_accepted_submissions, SUM(view1.total_views) AS total_views, SUM(view1.total_unique_views) AS total_unique_views FROM contests con INNER JOIN colleges c ON con.contest_id = c.contest_id INNER JOIN challenges ch ON c.college_id = ch.college_id INNER JOIN view_stats view1 ON ch.challenge_id = view1.challenge_id INNER JOIN submission_stats view2 ON ch.challenge_id = view2.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING SUM(view2.total_submissions) > 0 OR SUM(view2.total_accepted_submissions) > 0 OR SUM(view1.total_views) > 0 OR SUM(view1.total_unique_views) > 0 ORDER BY con.contest_id;
/* works in MySQL */
SELECT con.contest_id, con.hacker_id, con.name, SUM(total_submissions) AS ts, SUM(total_accepted_submissions) AS tas, SUM(total_views) AS tv, SUM(total_unique_views) AS tuv FROM contests con JOIN colleges col ON con.contest_id = col.contest_id JOIN challenges cha ON col.college_id = cha.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 cha.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 cha.challenge_id = ss.challenge_id GROUP BY con.contest_id, con.hacker_id, con.name HAVING ts + tas + tv + tuv > 0 ORDER BY contest_id;
run code is successfull but submit still loading?