WITH TTL AS(
SELECT
    USER_ID
    ,ENTRY_ID
    ,MAX(SUBMITTED_AT) AS LAST_SUB
    ,SUM(POINT) AS TTL_POINT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
    AND STATUS = 'AC'
    AND ENTRY_ID IS NOT NULL
GROUP BY
    USER_ID
    ,ENTRY_ID
HAVING
    TTL_POINT > 0
)
,EX AS(
SELECT
    E.USER_ID
    ,E.ENTRY_ID
    ,STRFTIME('%s', T.LAST_SUB) - STRFTIME('%s', E.STARTED_AT) AS EX_TIME
    , T.LAST_SUB
    ,E.STARTED_AT
    ,T.TTL_POINT
FROM
    TTL AS T
JOIN
    ENTRIES AS E
ON 
    T.ENTRY_ID = E.ENTRY_ID
GROUP BY
    E.USER_ID
    ,E.ENTRY_ID
)
,LAST_PER_PROBLEM AS(
SELECT
    USER_ID
    ,ENTRY_ID
    ,PROBLEM_ID
    ,MAX(SUBMITTED_AT) AS LAST_SUB
    ,STATUS
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = '2'
    AND ENTRY_ID IS NOT NULL
    AND STATUS ='AC'
GROUP BY
    USER_ID
    ,ENTRY_ID
    ,PROBLEM_ID
)
,WRONG AS(
SELECT
    S.USER_ID
    ,S.ENTRY_ID
    ,S.PROBLEM_ID
    ,S.SUBMITTED_AT
    ,S.STATUS
    ,L.LAST_SUB
FROM
    SUBMISSIONS AS S
JOIN
    LAST_PER_PROBLEM AS L
ON
    S.USER_ID = L.USER_ID
    AND S.ENTRY_ID = L.ENTRY_ID
    AND S.PROBLEM_ID = L.PROBLEM_ID
WHERE
    S.CONTEST_ID = '2'
    AND S.ENTRY_ID IS NOT NULL
    AND S.STATUS = 'WA'
ORDER BY
    S.USER_ID
)
,WRONG_CNT AS(
SELECT
    USER_ID
    ,ENTRY_ID 
    ,COUNT(*) AS WRONG_ANS
FROM
    WRONG
GROUP BY
    USER_ID
    ,ENTRY_ID
)
,WRONG_ANS AS(
SELECT
    EX.USER_ID
    ,EX.TTL_POINT
    ,EX.EX_TIME + 300*(CASE WHEN W.WRONG_ANS >= 0 THEN W.WRONG_ANS ELSE 0 END) AS EX_TIME
    ,CASE WHEN W.WRONG_ANS >= 0 THEN W.WRONG_ANS ELSE 0 END AS WRONG_ANS
FROM
    EX AS EX
LEFT JOIN
    WRONG_CNT AS W
ON
    EX.USER_ID = W.USER_ID
)

SELECT
    RANK()
        OVER(ORDER BY TTL_POINT DESC, EX_TIME ASC) AS RANK
    ,USER_ID
    ,TTL_POINT AS POINT
    ,EX_TIME
    ,WRONG_ANS
FROM
    WRONG_ANS
ORDER BY
    RANK ASC
    ,WRONG_ANS DESC
    ,USER_ID ASC