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.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

Sort by

recency

|

1410 Discussions

|

  • agarhari03
     + 0 comments

    select c.contest_id , c.hacker_id, c.name ,sum(total_submissions) ,sum(total_accepted_submissions) ,sum(total_views) ,sum(total_unique_views) from Contests c join Colleges co on c.contest_id = co.contest_id join Challenges ch on co.college_id = ch.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 ch.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 ch.challenge_id = ss.challenge_id group by c.contest_id, c.hacker_id, c.name having sum(total_submissions) != 0 or sum(total_accepted_submissions) != 0 or sum(total_views) !=0 or sum(total_unique_views) !=0 order by c.contest_id

  • rk85728
     + 0 comments
    SELECT 
    c.contest_id,
    c.hacker_id,
    c.name,
    COALESCE(SUM(ss.total_submissions), 0) AS total_submit,
    COALESCE(SUM(ss.total_accepted_submissions), 0) AS total_accpt,
    COALESCE(SUM(vs.total_views), 0) AS total_view,
    COALESCE(SUM(vs.total_unique_views), 0) AS total_uni_view
FROM Contests c
JOIN Colleges clg ON c.contest_id = clg.contest_id
JOIN Challenges ch ON clg.college_id = ch.college_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 ch.challenge_id = ss.challenge_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 ch.challenge_id = vs.challenge_id
GROUP BY c.contest_id, c.hacker_id, c.name
HAVING 
    COALESCE(SUM(ss.total_submissions), 0) +
    COALESCE(SUM(ss.total_accepted_submissions), 0) +
    COALESCE(SUM(vs.total_views), 0) +
    COALESCE(SUM(vs.total_unique_views), 0) > 0
ORDER BY c.contest_id;

  • manojkapkoti1008
     + 0 comments

    why it is not working SELECT c.contest_id,
    c.hacker_id, c.name, SUM(ss.total_submissions) AS sum_of_total_submissions, SUM(ss.total_accepted_submissions) AS total_acc_submissions, SUM(vs.total_views) AS sum_of_total_views, SUM(vs.total_unique_views) AS sum_total_unique_views FROM contests AS c JOIN colleges AS co ON c.contest_id = co.contest_id JOIN challenges AS ch ON co.college_id = ch.college_id JOIN view_stats AS vs ON ch.challenge_id = vs.challenge_id JOIN submission_stats AS ss ON ch.challenge_id = ss.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name HAVING SUM(ss.total_submissions) > 0 OR SUM(ss.total_accepted_submissions) > 0 OR SUM(vs.total_views) > 0 OR SUM(vs.total_unique_views) > 0 ORDER BY c.contest_id;

  • satyamtyagi121
     + 0 comments

    with cte as ( select challenge_id, sum(isnull(total_submissions,0)) as total_submission, sum(isnull(total_accepted_submissions,0)) as total_accepted_submission from submission_stats group by challenge_id), cte1 as( select challenge_id, SUM(ISNULL(total_views, 0)) AS total_views, SUM(ISNULL(total_unique_views, 0)) AS total_unique_views FROM view_stats GROUP BY challenge_id) select con.contest_id, con.hacker_id, con.name, SUM(ISNULL(c.total_submission, 0)) AS total_submission, SUM(ISNULL(c.total_accepted_submission, 0)) AS total_accepted_submission, SUM(ISNULL(ct.total_views, 0)) AS total_views, SUM(ISNULL(ct.total_unique_views, 0)) AS total_unique_view from contests as con join colleges as col on con.contest_id=col.contest_id join challenges as ch on col.college_id=ch.college_id left join cte as c on ch.challenge_id=c.challenge_id left join cte1 as ct on ch.challenge_id=ct.challenge_id group by con.contest_id, con.hacker_id, con.name having SUM(ISNULL(c.total_submission, 0)) + SUM(ISNULL(c.total_accepted_submission, 0))+ SUM(ISNULL(ct.total_views, 0))+ SUM(ISNULL(ct.total_unique_views, 0)) >0 order by con.contest_id;

  • lizasizas
     + 0 comments

    WITH new_submission_stats AS ( SELECT s.challenge_id, s.total_submissions, s.total_accepted_submissions, c.college_id, o.contest_id, h.hacker_id, h.name FROM submission_stats s LEFT JOIN challenges c ON s.challenge_id = c.challenge_id LEFT JOIN colleges o ON c.college_id = o.college_id LEFT JOIN contests h ON o.contest_id = h.contest_id ), new_view_stats AS ( SELECT v.challenge_id, v.total_views, v.total_unique_views, c.college_id, o.contest_id, h.hacker_id, h.name FROM view_stats v LEFT JOIN challenges c ON v.challenge_id = c.challenge_id LEFT JOIN colleges o ON c.college_id = o.college_id LEFT JOIN contests h ON o.contest_id = h.contest_id ), sum_submission_stats AS ( SELECT DISTINCT contest_id, hacker_id, name, SUM(total_submissions) OVER (PARTITION BY contest_id) AS sum_s, SUM(total_accepted_submissions) OVER (PARTITION BY contest_id) AS sum_as FROM new_submission_stats ), sum_view_stats AS ( SELECT DISTINCT contest_id, hacker_id, name, SUM(total_views) OVER (PARTITION BY contest_id) AS sum_v, SUM(total_unique_views) OVER (PARTITION BY contest_id) AS sum_uv FROM new_view_stats ), final_table AS ( SELECT ss.contest_id, ss.hacker_id, ss.name, ss.sum_s, ss.sum_as, vs.sum_v, vs.sum_uv FROM sum_submission_stats ss LEFT JOIN sum_view_stats vs ON ss.contest_id = vs.contest_id ) SELECT * FROM final_table WHERE sum_s IS NOT NULL AND sum_as IS NOT NULL AND sum_v IS NOT NULL AND sum_uv IS NOT NULL;