--誤答数、回答時間
WITH T1 AS (
SELECT
    SUB.USER_ID,
    SUM(CASE STATUS WHEN 'WA' THEN 1 ELSE 0 END) AS WRONG_ANS,
    STRFTIME('%s',LAST.SUBMITTED_AT) - STRFTIME('%s',STARTED_AT) AS TIME,
    (STRFTIME('%s',LAST.SUBMITTED_AT) - STRFTIME('%s',STARTED_AT))+SUM(CASE STATUS WHEN 'WA' THEN 1 ELSE 0 END)*300 AS EX_TIME
FROM SUBMISSIONS AS SUB INNER JOIN ENTRIES AS ENT ON ENT.CONTEST_ID=SUB.CONTEST_ID AND	ENT.USER_ID=SUB.USER_ID
INNER JOIN(
    SELECT
        USER_ID,
        SUBMITTED_AT,
        MAX(SUBMITTED_AT) AS LAST_AC
    FROM SUBMISSIONS
    WHERE ENTRY_ID IS NOT NULL AND STATUS='AC' AND CONTEST_ID='2'
    GROUP BY USER_ID
) AS LAST ON SUB.USER_ID=LAST.USER_ID
WHERE ENT.ENTRY_ID IS NOT NULL AND LAST.SUBMITTED_AT>=SUB.SUBMITTED_AT
GROUP BY ENT.USER_ID
ORDER BY ENT.USER_ID
)
, T2 AS (--得点
SELECT
    USER_ID,
    SUM(POINT) AS POINT
FROM SUBMISSIONS
WHERE ENTRY_ID IS NOT NULL AND CONTEST_ID='2'
GROUP BY USER_ID
HAVING SUM(POINT)>0
)
SELECT
    RANK() OVER (ORDER BY POINT DESC,EX_TIME) AS RANK,
    T1.USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS
FROM T1 INNER JOIN T2 ON T1.USER_ID=T2.USER_ID
ORDER BY RANK,WRONG_ANS,T1.USER_ID;