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.
  • HackerRank Home

    HackerRank

  • |
  • Prepare
  • Certify
  • Compete
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

Problem
Submissions
Leaderboard
Discussions

Sort 873 Discussions, By:

recency

Please Login in order to post a comment

  • engahmedabdulla1
    3 hours ago+ 0 comments

    Explanation: you cannot do it with one query, because submission_stat and view_stat tables are indepandent of each other, if you join them with one statement you will get wrong result because it will double the number of rows having contest_id so, you have to process them with different queries

    with cte1 as (
        select 
            con.contest_id,
            con.hacker_id,
            con.name,
            sum(total_submissions) as totSub,
            sum(total_accepted_submissions) as totAcSub
        from contests con
        join colleges col on col.contest_id = con.contest_id
        join challenges cha on cha.college_id = col.college_id
        join Submission_Stats sub on sub.challenge_id = cha.challenge_id 
        group by con.contest_id,con.hacker_id,con.name
    ),
    
    cte2 as
    (
        select 
            con.contest_id,
            sum(total_views) as totVie,
            sum(total_unique_views) as totUnVie
        from contests con
        join colleges col on col.contest_id = con.contest_id
        join challenges cha on cha.college_id = col.college_id
        join View_Stats vi on vi.challenge_id = cha.challenge_id
        group by con.contest_id
    )
    
    select 
        cte1.contest_id,
        cte1.hacker_id,
        cte1.name,
        totSub,
        totAcSub,
        totVie,
        totUnVie
    from cte1
    join cte2 on cte1.contest_id = cte2.contest_id
    order by cte1.contest_id
    
    0|
    Permalink
  • hongntfx19807
    16 hours ago+ 0 comments
    1. ect1 => total_submissions & accepted_submissions
    2. ect2 => total_views & total_unique_views
    3. ect1 FULL JOIN ect2
    with ect1 as (SELECT Co.contest_id, Co.hacker_id, Co.name, 
        sum(ss.total_submissions) as 's1', 
        sum(ss.total_accepted_submissions) as 's2'
        FROM Contests co
        JOIN Colleges col ON Co.contest_id  = Col.contest_id 
        JOIN Challenges ch ON Col.college_id = Ch.college_id
        JOIN Submission_Stats ss ON Ch.challenge_id = Ss.challenge_id
        GROUP BY Co.contest_id, Co.hacker_id, Co.name),
    
    ect2 as (SELECT Co.contest_id, Co.hacker_id, Co.name, 
        sum(vs.total_views) as 's3', 
        sum(vs.total_unique_views) as 's4'
        FROM Contests co
        JOIN Colleges col ON Co.contest_id  = Col.contest_id 
        JOIN Challenges ch ON Col.college_id = Ch.college_id
        JOIN View_Stats vs ON Ch.challenge_id = vs.challenge_id
        GROUP BY Co.contest_id, Co.hacker_id, Co.name)
    
    SELECT ect1.contest_id, ect1.hacker_id, ect1.name, 
        ect1.s1, ect1.s2, ect2.s3, ect2.s4
    FROM ect1 FULL JOIN ect2 ON ect1.contest_id = ect2.contest_id and ect1.hacker_id = ect2.hacker_id and ect1.name = ect2.name
    WHERE  ect1.s1+ ect1.s2+ ect2.s3+ ect2.s4 <>0
    ORDER BY ect1.contest_id
    
    0|
    Permalink
  • Zyad_Mohamed21
    23 hours ago+ 1 comment

    anyone know what is wrong?

    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 
    JOIN Colleges cl ON c.contest_id = cl.contest_id 
    JOIN Challenges ch ON ch.college_id = cl.college_id 
    JOIN View_Stats v ON v.challenge_id = ch.challenge_id 
    JOIN Submission_Stats s ON s.challenge_id = ch.challenge_id 
    GROUP BY c.contest_id, c.hacker_id, c.name 
    HAVING SUM(s.total_submissions) + SUM(s.total_accepted_submissions) + SUM(v.total_views) + SUM(v.total_unique_views) > 0 
    ORDER BY c.contest_id
    
    0|
    Permalink
  • sudheerchowdar16
    2 days ago+ 0 comments

    can anyone explain, where i'm missing the logic ?

    WITH temp AS ( SELECT b.contest_id, SUM(total_submissions) AS sum_total_submissions, SUM(total_accepted_submissions) AS sum_total_accepted_submissions, SUM(total_views) AS sum_total_views, SUM(total_unique_views) AS sum_total_unique_views FROM colleges AS b LEFT JOIN challenges AS c ON b.college_id = c.college_id LEFT JOIN view_stats AS d ON c.challenge_id = d.challenge_id LEFT JOIN submission_stats AS e ON c.challenge_id = e.challenge_id GROUP BY b.contest_id ) SELECT a.contest_id,f.hacker_id,f.name,sum_total_submissions,sum_total_accepted_submissions, sum_total_views,sum_total_unique_views FROM temp as a inner join contests as f on a.contest_id=f.contest_id where (sum_total_submissions+ sum_total_accepted_submissions+ sum_total_views + sum_total_unique_views)> 0 order by a.contest_id ;

    0|
    Permalink
  • kiranbi0217
    2 days ago+ 0 comments

    can someone help me what is wrong in my code:

    with college_id as
        (select  co.name, co.contest_id, co.hacker_id, ch.challenge_id from contests co
        join colleges cl on co.contest_id = cl.contest_id
        join challenges ch on cl.college_id = ch.college_id)
    select c.contest_id, c.hacker_id, c.name, sum(s.total_submissions), sum(s.total_accepted_submissions), sum(v.total_views), sum(v.total_unique_views) from college_id c
    join submission_stats s on c.challenge_id = s.challenge_id
    join view_stats v on c.challenge_id = v.challenge_id
    where (s.total_submissions + s.total_accepted_submissions + v.total_views + v.total_unique_views) <> 0
    group by c.contest_id, c.hacker_id, c.name
    order by c.contest_id;
    
    0|
    Permalink
Load more conversations

Need Help?


View top submissions
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy