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

  • j_ibarralemp
     + 1 comment

    Took a little advice from previous posts here, but arrived at a solution finally.

    Careful with the null values and group all to contest level with college table before doing the final join with contests.

    SELECT con.contest_id,
    con.hacker_id,
    con.name,
    ts,
    tas,
    tv,
    tuv
FROM contests AS con
INNER JOIN (
    SELECT coll.contest_id,
        SUM(COALESCE(ts,0)) AS ts,
        SUM(COAlESCE(tas,0)) AS tas,
        SUM(COALESCE(tv,0)) AS tv,
        SUM(COALESCE(tuv,0)) AS tuv
    FROM challenges AS chall
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_submissions,0)) AS ts,
                SUM(COALESCE(total_accepted_submissions,0)) AS tas
            FROM submission_stats AS sstats
            GROUP BY challenge_id
            ) AS sstats
    ON chall.challenge_id = sstats.challenge_id
    LEFT JOIN(
            SELECT challenge_id,
                SUM(COALESCE(total_views,0)) AS tv,
                SUM(COALESCE(total_unique_views,0)) AS tuv
            FROM view_stats AS vstats
            GROUP BY challenge_id
            ) AS vstats
    ON chall.challenge_id = vstats.challenge_id
    LEFT JOIN colleges AS coll
    ON chall.college_id = coll.college_id
    GROUP BY coll.contest_id) AS fstats
ON con.contest_id = fstats.contest_id
ORDER BY con.contest_id
;