WITH WRONG_ANS_TOTAL AS (
    SELECT S.ENTRY_ID,
        COUNT(*) AS WRONG_ANS_COUNT
    FROM SUBMISSIONS S
        INNER JOIN (
            SELECT ENTRY_ID,
                PROBLEM_ID,
                MAX(SUBMITTED_AT) AS MAX_SUBMITTED_AT
            FROM SUBMISSIONS S1
            WHERE S1.STATUS = 'AC'
                AND S1.CONTEST_ID = 2
                AND S1.ENTRY_ID IS NOT NULL
            GROUP BY ENTRY_ID,
                PROBLEM_ID
        ) AS S2 ON S.ENTRY_ID = S2.ENTRY_ID
        AND S.PROBLEM_ID = S2.PROBLEM_ID
        AND S.SUBMITTED_AT < S2.MAX_SUBMITTED_AT
    GROUP BY S.ENTRY_ID
),
TIME_AND_ENTRY AS (
    SELECT ENTRY_ID,
        SUM(POINT) POINT,
        MAX(SUBMITTED_AT) AS MAX_SUBMITTED_AT
    FROM SUBMISSIONS S1
    WHERE S1.STATUS = 'AC'
        AND S1.CONTEST_ID = 2
        AND S1.ENTRY_ID IS NOT NULL
    GROUP BY ENTRY_ID
)
SELECT RANK() OVER(
        ORDER BY t.POINT DESC,
            STRFTIME('%s', t.MAX_SUBMITTED_AT) - STRFTIME('%s', e.STARTED_AT) + IFNULL(w.WRONG_ANS_COUNT, 0) * 300 ASC
    ) RANK,
    USER_ID USER_ID,
    t.POINT POINT,
    STRFTIME('%s', t.MAX_SUBMITTED_AT) - STRFTIME('%s', e.STARTED_AT) + IFNULL(w.WRONG_ANS_COUNT, 0) * 300 AS EX_TIME,
    NULLIF(w.WRONG_ANS_COUNT, 0) WRONG_ANS
FROM ENTRIES e
    LEFT OUTER JOIN WRONG_ANS_TOTAL w ON e.ENTRY_ID = w.ENTRY_ID
    JOIN TIME_AND_ENTRY t ON e.ENTRY_ID = t.ENTRY_ID
WHERE E.CONTEST_ID = 2 -- 忘れてた!
ORDER BY RANK,
    WRONG_ANS,
    USER_ID