WITH MT AS --誤答数
(SELECT USER_ID
,SUM(ANS-1) AS WRONG_ANS
FROM
(SELECT
*,
ROW_NUMBER()
        OVER(
        PARTITION BY USER_ID,PROBLEM_ID
        ORDER BY SUBMIT_ID)
AS ANS
FROM 

(SELECT *
FROM SUBMISSIONS AS A
JOIN
(SELECT USER_ID,PROBLEM_ID,SUBMITTED_AT AS LASTACTIME FROM SUBMISSIONS WHERE STATUS = 'AC' GROUP BY PROBLEM_ID,USER_ID) AS B
ON A.USER_ID = B.USER_ID AND A.PROBLEM_ID = B.PROBLEM_ID
WHERE SUBMITTED_AT <= LASTACTIME)

WHERE CONTEST_ID = 2 AND ENTRY_ID IS NOT NULL AND STATUS IN ('AC','WA')
)
WHERE STATUS = 'AC' 
 
GROUP BY USER_ID
)
,EXT AS --試験時間
(SELECT 
USER_ID,
PR.PRE_EX_TIME  + (MT.WRONG_ANS * 300) AS EX_TIME
FROM
(SELECT
USER_ID,
STRFTIME ('%s',MAX(SUBMITTED_AT))-
STRFTIME ('%s',EN. STARTED_AT) AS PRE_EX_TIME
FROM SUBMISSIONS AS SUB
LEFT JOIN ENTRIES AS EN
USING(USER_ID)
WHERE SUB.CONTEST_ID = 2 AND SUB.ENTRY_ID IS NOT NULL AND STATUS = 'AC'
GROUP BY USER_ID
) AS PR
JOIN MT
USING(USER_ID)
)
,PT AS --点数
(SELECT
USER_ID,
SUM(POINT) AS POINT
FROM SUBMISSIONS
WHERE CONTEST_ID = 2 AND ENTRY_ID IS NOT NULL AND STATUS = 'AC'
GROUP BY 1
)

SELECT
USER_ID,
EXT.EX_TIME,
MT.WRONG_ANS,
PT.POINT,
RANK()
OVER(
    ORDER BY POINT DESC, EX_TIME
) AS RANK
FROM MT AS MT
JOIN EXT AS EXT
USING(USER_ID)
JOIN PT AS PT
USING(USER_ID)
ORDER BY RANK  , WRONG_ANS , USER_ID
;