• + 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