Sort by

recency

|

1284 Discussions

|

  • + 0 comments

    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

  • + 0 comments

    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;

  • + 0 comments
    -- For MS SQL at this platform, but it works on MySQL installed at my laptop.
    with subm as (
        select challenge_id
            , sum(total_submissions) as total_submissions
            , sum(total_accepted_submissions) as total_accepted_submissions
        from submission_stats 
        group by challenge_id
    )
    , views as (
        select challenge_id
            , sum(total_views) as total_views
            , sum(total_unique_views) as total_unique_views
        from view_stats 
        group by challenge_id
    )
    
    select con.contest_id
            , con.hacker_id
            , con.name
            , sum(total_submissions)
            , sum(total_accepted_submissions)
            , sum(total_views)
            , sum(total_unique_views)
    from contests as con 
    join colleges as col on con.contest_id = col.contest_id 
    join challenges as cha on  col.college_id = cha.college_id 
    left join views on cha.challenge_id = views.challenge_id
    left join subm on cha.challenge_id = subm.challenge_id
    
    group by con.contest_id, con.hacker_id, con.name
    having sum(total_submissions) + 
            sum(total_accepted_submissions) + 
            sum(total_views) + 
            sum(total_unique_views) > 0
    order by con.contest_id
    
  • + 0 comments

    /* 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;

  • + 0 comments

    run code is successfull but submit still loading?