-- エントリーに紐づいた提出
WITH S AS (
    SELECT * FROM SUBMISSIONS
    NATURAL JOIN ENTRIES
)
-- AC時刻
, A AS(
    SELECT USER_ID, PROBLEM_ID, SUBMITTED_AT
    FROM S
    WHERE STATUS = 'AC'
)
-- 誤答数
, B AS (
    SELECT USER_ID, COUNT(*) WRONG_ANS
    FROM S
    JOIN A USING(USER_ID, PROBLEM_ID)
    WHERE STATUS='WA' AND A.SUBMITTED_AT > S.SUBMITTED_AT
    GROUP BY USER_ID
)
-- メイン1
, C AS (
    SELECT
    USER_ID
    , SUM(POINT) POINT
    , STRFTIME('%s', MAX(IIF(STATUS='AC', SUBMITTED_AT, NULL)))
        -STRFTIME('%s', MAX(STARTED_AT)) tm
    FROM S
    GROUP BY USER_ID
    HAVING POINT > 0
)
-- 誤答からEXT_TIMEの計算
, D AS(
    SELECT
    *,
    tm + 300 * COALESCE(WRONG_ANS, 0) EX_TIME
    FROM C
    LEFT JOIN B USING(USER_ID) -- 誤答がないかも
)
SELECT
RANK() OVER(ORDER BY POINT DESC, EX_TIME) RANK
, USER_ID,POINT,EX_TIME,COALESCE(WRONG_ANS, 0) WRONG_ANS
FROM D
ORDER BY RANK