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

  • BlurFeng
     + 0 comments

    Because I didn't fully understand INNER JOIN, I spent some time on this. INNER JOINs are chained, so if any condition is not met, the entire row is discarded. Therefore, data like 'TV=NULL, TUV=NULL, TS=8, TAS=4' would be discarded. Below is my answer.

    SELECT
    Co.contest_id,
    Co.hacker_id,
    Co.name,
    TS,
    TAS,
    TV,
    TUV
FROM Contests AS Co
JOIN (
    SELECT
        Col.contest_id AS contest_id,
        SUM(TV) AS TV,
        SUM(TUV) AS TUV,
        SUM(TS) AS TS,
        SUM(TAS) AS TAS
    FROM Colleges AS Col
    JOIN Challenges AS Ch ON Ch.college_id = Col.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 VS.challenge_id = Ch.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 Col.contest_id
) AS CD ON CD.contest_id = Co.contest_id
WHERE TV + TUV + TS + TAS > 0
ORDER BY Co.contest_id