WITH T3 AS (
SELECT ENT.USER_ID,SUB.STATUS,
    T2.POINT,ENT.STARTED_AT 開始時間,SUB.SUBMITTED_AT 提出時間,
    strftime('%s', SUB.SUBMITTED_AT)-strftime('%s', STARTED_AT) AS 経過時間,T1.SUBMITTED_AT AS 最後のAC,
    SUM(CASE WHEN SUB.STATUS='WA' THEN 1 ELSE 0 END) OVER (PARTITION BY ENT.USER_ID) AS WRONG_ANS,
    strftime('%s', SUB.SUBMITTED_AT)-strftime('%s', STARTED_AT) + (300 * SUM(CASE WHEN SUB.STATUS='WA' THEN 1 ELSE 0 END) OVER (PARTITION BY ENT.USER_ID)) AS EX_TIME
FROM SUBMISSIONS AS SUB
INNER JOIN ENTRIES AS ENT ON ENT.USER_ID=SUB.USER_ID AND ENT.CONTEST_ID=SUB.CONTEST_ID
INNER JOIN (
SELECT USER_ID,STATUS,SUBMITTED_AT,RANK() OVER (PARTITION BY USER_ID ORDER BY SUBMITTED_AT DESC) AS 最後のAC
FROM SUBMISSIONS
WHERE STATUS='AC' AND ENTRY_ID IS NOT NULL
) AS T1 ON ENT.USER_ID=T1.USER_ID AND 最後のAC=1
INNER JOIN (
SELECT USER_ID,SUM(POINT) AS 'POINT'
FROM SUBMISSIONS
WHERE ENTRY_ID IS NOT NULL
GROUP BY USER_ID
HAVING POINT<>0
ORDER BY POINT DESC
) AS T2 ON ENT.USER_ID=T2.USER_ID
WHERE SUB.ENTRY_ID IS NOT NULL AND strftime('%s', T1.SUBMITTED_AT)-strftime('%s', SUB.SUBMITTED_AT)>=0
ORDER BY ENT.USER_ID
)
SELECT RANK() OVER (ORDER BY POINT DESC,MAX(EX_TIME)),
    USER_ID,POINT,EX_TIME,WRONG_ANS
FROM T3
GROUP BY USER_ID