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

  • rom_adv
     + 0 comments

    This problem seemed simple at first but it really makes you think hard about how you're using your JOINs like what would happen if you FULL JOIN 2 tables with different number of rows for their common ID column. Also a good case for using COALESCE.

    Answer:

    WITH st AS (
    SELECT col.contest_id
    ,SUM(st.total_views) total_views
    ,SUM(st.total_unique_views) total_unique_views
    ,SUM(st.total_submissions) total_submissions
    ,SUM(st.total_accepted_submissions) total_accepted_submissions
    FROM colleges col
    INNER JOIN (
        SELECT college_id
            ,SUM(st.total_views) total_views
            ,SUM(st.total_unique_views) total_unique_views
            ,SUM(st.total_submissions) total_submissions
            ,SUM(st.total_accepted_submissions) total_accepted_submissions
        FROM challenges cha
        INNER JOIN (
            SELECT COALESCE(v.challenge_id, s.challenge_id) AS challenge_id
            ,COALESCE(
                total_views
                ,0
            ) AS total_views
            ,COALESCE(
                total_unique_views
                ,0
            ) AS total_unique_views
            ,COALESCE(
                total_submissions
                ,0
            ) AS total_submissions
            ,COALESCE(
                total_accepted_submissions
                ,0
            ) AS total_accepted_submissions
            FROM (
                SELECT challenge_id
                ,SUM(total_views) AS total_views
                ,SUM(total_unique_views) AS total_unique_views
                FROM view_stats v
                GROUP BY challenge_id
            ) v
            FULL OUTER JOIN (
                SELECT challenge_id
                ,SUM(total_submissions) AS total_submissions
                ,SUM(total_accepted_submissions) AS total_accepted_submissions
                FROM submission_stats
                GROUP BY challenge_id
            ) s
            ON v.challenge_id = s.challenge_id
        ) st
        ON cha.challenge_id = st.challenge_id
        GROUP BY college_id
    ) st
    ON col.college_id = st.college_id
    GROUP BY col.contest_id
)
SELECT con.contest_id
,con.hacker_id
,con.name
,st.total_submissions
,st.total_accepted_submissions
,st.total_views
,total_unique_views
FROM contests con
INNER JOIN st
ON con.contest_id = st.contest_id
ORDER BY con.contest_id