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
  • Prepare
    NEW
  • Certify
  • Compete
  • Career Fair
  • Hiring developers?
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Interviews
  5. Discussions

Interviews

Problem
Submissions
Leaderboard
Discussions

Sort 781 Discussions, By:

recency

Please Login in order to post a comment

  • mayank912912
    2 days ago+ 0 comments

    MYSQL Solution

    select c.contest_id, c.hacker_id, c.name, sum(ss.ts) as a, sum(ss.tas) as b, sum(vs.tv) as c, sum(vs.tuv) as d
    from contests as con 
    
    inner join colleges as co
    on con.contest_id = co.contest_id
    inner join challenges as ch
    on ch.college_id = co.college_id
    
    left join (select challenge_id, sum(total_views) as tv, sum(total_unique_views) as tuv from view_stats group by challenge_id) as vs
    on ch.challenge_id = vs.challenge_id
    
    left join (select challenge_id, sum(total_submissions) as ts, sum(total_accepted_submissions) as tas from submission_stats group by challenge_id) as ss
    on ss.challenge_id = ch.challenge_id
    
    group by 1,2,3
    having (a+b+c+d)<>0
    order by 1
    
    0|
    Permalink
  • htulasiraonitc
    1 week ago+ 0 comments

    -- MySQL Solution

    SELECT * FROM ( SELECT c.contest_id, c.hacker_id, c.name,
    SUM(A) A, SUM(B) B, SUM(C) C, SUM(D) D FROM Contests c LEFT JOIN Colleges co ON co.contest_id = c.contest_id LEFT JOIN Challenges ch ON ch.college_id = co.college_id LEFT JOIN ( SELECT challenge_id, SUM(v.total_views) C, SUM(v.total_unique_views) D FROM View_Stats v GROUP BY challenge_id) E ON E.challenge_id = ch.challenge_id LEFT JOIN ( SELECT challenge_id, SUM(s.total_submissions) A, SUM(s.total_accepted_submissions) B FROM Submission_Stats s GROUP BY challenge_id) F ON F.challenge_id = ch.challenge_id GROUP BY c.contest_id, c.hacker_id, c.name ORDER BY c.contest_id ) AS G WHERE A != 0 AND B != 0 AND C != 0 AND D != 0;

    0|
    Permalink
  • eenaagrawal08
    1 week ago+ 0 comments

    My sql solution:

    select c.contest_id, c.hacker_id, c.name, coalesce(s.total_Submission,0) total_Submission, coalesce(s.total_accepted_Submission,0) total_accepted_Submission, coalesce(v.total_views,0) total_views, coalesce(v.total_unique_views,0) total_unique_views from contests c left join ( select
    co.contest_id, sum(ss.total_Submissions) total_Submission, sum(ss.total_accepted_Submissions) total_accepted_Submission from colleges co inner join challenges ch on ch.college_id=co.college_id inner join Submission_Stats ss on ss.challenge_id= ch.challenge_id group by co.contest_id ) s on s.contest_id=c.contest_id left join ( select co.contest_id, sum(vs.total_views) total_views, sum(vs.total_unique_views) total_unique_views from colleges co inner join challenges ch on ch.college_id=co.college_id inner join view_stats vs on vs.challenge_id= ch.challenge_id group by co.contest_id ) v on v.contest_id=c.contest_id where total_Submission+total_accepted_Submission+total_views+ total_unique_views>0 ;

    0|
    Permalink
  • marieduarda_g_m1
    1 week ago+ 0 comments
    WITH submissions AS (
        SELECT
            co.contest_id,
            SUM(ss.total_submissions) tot_sub,
            SUM(ss.total_accepted_submissions) tot_acc_sub
        FROM 
            Colleges co
                JOIN
            Challenges ch ON co.college_id = ch.college_id 
                JOIN
            Submission_Stats ss ON ch.challenge_id = ss.challenge_id
        GROUP BY co.contest_id
    ), 
    views AS (
        SELECT
            co.contest_id,
            SUM(vs.total_views) tot_views,
            SUM(vs.total_unique_views) tot_uni_views
        FROM 
            Colleges co
                JOIN
            Challenges ch ON co.college_id = ch.college_id 
                JOIN
            View_Stats vs ON ch.challenge_id = vs.challenge_id
        GROUP BY co.contest_id
    )
    
    SELECT 
        c.contest_id,
        c.hacker_id,
        c.name,
        s.tot_sub,
        s.tot_acc_sub,
        v.tot_views,
        v.tot_uni_views
    FROM
        Contests c
            JOIN
        submissions s ON c.contest_id = s.contest_id
            JOIN
        views v ON s.contest_id = v.contest_id
    
    0|
    Permalink
  • muskansahay301
    2 weeks ago+ 0 comments

    SELECT A.CONTEST_ID, A.HACKER_ID, A.NAME, SUM(TOTAL_SUBMISSION) AS TOTAL_SUBMISSIONS, SUM(TOTAL_ACCEPTED_SUBMISSION) AS TOTAL_ACCEPTED_SUBMISSIONS, SUM(TOTAL_VIEW) AS TOTAL_VIEWS, SUM(TOTAL_UNIQUE_VIEW) 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_VIEW, SUM(TOTAL_UNIQUE_VIEWS) AS TOTAL_UNIQUE_VIEW 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_SUBMISSION, SUM(TOTAL_ACCEPTED_SUBMISSIONS) AS TOTAL_ACCEPTED_SUBMISSION 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;

    0|
    Permalink
Load more conversations

Need Help?


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