--ENTRIEをjoinすれば、SUBMISSIONS内のNULLは消える。

WITH TEMP AS(
    SELECT * FROM ENTRIES NATURAL JOIN SUBMISSIONS
    WHERE CONTEST_ID = 2
)

,LAST_AC_TIME_RANK AS(
    SELECT
        USER_ID
        ,SUBMITTED_AT
        ,RANK() OVER (PARTITION BY USER_ID ORDER BY SUBMITTED_AT DESC) AS AC_RANK
    FROM TEMP
    WHERE STATUS = 'AC'
)

,LAST_AC_TIME AS(
    SELECT
        USER_ID
        ,STARTED_AT
        ,SUBMITTED_AT
        ,STRFTIME('%s', SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) AS AC_TIME
    FROM TEMP NATURAL JOIN LAST_AC_TIME_RANK
    WHERE AC_RANK = 1
)

,LAST_AC_TIME_MAX AS(
    SELECT
        USER_ID
        ,PROBLEM_ID
        ,MAX(SUBMITTED_AT) AS PROB_MAX_AC_TIME
        FROM TEMP
        WHERE STATUS = 'AC'
        GROUP BY USER_ID, PROBLEM_ID
)

,WA_COUNT AS(
    SELECT
        USER_ID
        ,PROBLEM_ID
        ,COUNT(STATUS) AS WA_COUNTS
        FROM TEMP NATURAL JOIN LAST_AC_TIME_MAX
        WHERE STATUS != 'AC'
        GROUP BY USER_ID
        HAVING SUBMITTED_AT < PROB_MAX_AC_TIME
)

,SUM_POINT AS(
    SELECT
        USER_ID
        ,CONTEST_ID
        ,SUM(POINT) AS POINT
    FROM TEMP
    WHERE STATUS = 'AC'
    GROUP BY
        USER_ID
        ,CONTEST_ID
    HAVING SUM(POINT) > 0
)

SELECT
    RANK() OVER (ORDER BY s.POINT DESC, l.AC_TIME + IFNULL(w.WA_COUNTS,0) * 300)AS RANK
    ,l.USER_ID AS USER_ID
    ,s.POINT AS POINT
    ,l.AC_TIME + IFNULL(w.WA_COUNTS,0) * 300 AS EX_TIME
    ,IFNULL(w.WA_COUNTS,0) AS WRONG_ANS
FROM LAST_AC_TIME AS l
LEFT OUTER JOIN WA_COUNT AS w
ON l.USER_ID = w.USER_ID
LEFT OUTER JOIN SUM_POINT AS s
ON l.user_id = s.user_id
ORDER BY RANK, WRONG_ANS, L.USER_ID