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 SUBMISSIONS
WHERE CONTEST_ID = 2 AND ENTRY_ID IS NOT NULL
)
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
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 DESC
) AS RANK

FROM MT AS MT
JOIN EXT AS EXT
USING(USER_ID)
JOIN PT AS PT
USING(USER_ID)
ORDER BY RANK
;