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

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

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