WITH MT AS --誤答数
(SELECT ENTRY_ID
,SUM(ANS-1) AS WRONG_ANS
FROM
(SELECT
*,
ROW_NUMBER()
        OVER(
        PARTITION BY ENTRY_ID,PROBLEM_ID
        ORDER BY SUBMIT_ID)
AS ANS
FROM 
(SELECT *
FROM SUBMISSIONS AS A
JOIN
(SELECT ENTRY_ID,PROBLEM_ID,SUBMITTED_AT AS LASTACTIME FROM SUBMISSIONS WHERE STATUS = 'AC' GROUP BY PROBLEM_ID,ENTRY_ID) AS B
ON A.ENTRY_ID = B.ENTRY_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 ENTRY_ID
)
,EXT AS --試験時間
(SELECT 
ENTRY_ID,
PR.PRE_EX_TIME  + (MT.WRONG_ANS * 300) AS EX_TIME
FROM
(SELECT
ENTRY_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(ENTRY_ID)
WHERE SUB.CONTEST_ID = 2 AND SUB.ENTRY_ID IS NOT NULL AND STATUS = 'AC'
GROUP BY ENTRY_ID
) AS PR
JOIN MT
USING(ENTRY_ID)
)
,PT AS --点数
(SELECT
ENTRY_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 PT.POINT DESC, EX_TIME
) AS RANK
FROM MT AS MT
JOIN EXT AS EXT
USING(ENTRY_ID)
JOIN PT AS PT
USING(ENTRY_ID)
JOIN SUBMISSIONS AS SUB
USING(ENTRY_ID)
GROUP BY USER_ID
ORDER BY RANK  , WRONG_ANS , USER_ID
;