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

Interviews

Problem
Submissions
Leaderboard
Discussions

    You are viewing a single comment's thread. Return to all comments →

  • hongntfx19807
    4 months 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
  • Blog
  • Scoring
  • Environment
  • FAQ
  • About Us
  • Support
  • Careers
  • Terms Of Service
  • Privacy Policy