WITH POINT AS(
SELECT
    USER_ID
    ,SUM(POINT) AS POINT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = 2
    AND ENTRY_ID != 'NULL'
GROUP BY
    USER_ID
)
,LASTTIME AS(
SELECT
    USER_ID
    ,MAX(SUBMITTED_AT) AS LAST_TIME
    ,STATUS
FROM 
    SUBMISSIONS 
WHERE
    CONTEST_ID = 2
    AND STATUS = 'AC'
    AND ENTRY_ID != 'NULL'
GROUP BY 
    USER_ID
)
,EXTIME AS(
SELECT
    EN.USER_ID
    ,STRFTIME('%s',LT.LAST_TIME) - STRFTIME('%s',EN.STARTED_AT) AS EX_TIME
FROM
    LASTTIME AS LT
LEFT JOIN
    ENTRIES AS EN
ON
    LT.USER_ID = EN.USER_ID
WHERE
    CONTEST_ID = 2
    AND STATUS = 'AC'
    AND ENTRY_ID != 'NULL'
GROUP BY
    EN.USER_ID
)
,EACH_LT AS(
SELECT
    USER_ID
    ,PROBLEM_ID
    ,MAX(SUBMITTED_AT) AS ELT
FROM
    SUBMISSIONS
WHERE
    CONTEST_ID = 2
    AND STATUS = 'AC'
    AND ENTRY_ID != 'NULL'
GROUP BY
    USER_ID
    ,PROBLEM_ID
)
,WRONG AS(
SELECT
    SUB.USER_ID
    ,SUB.PROBLEM_ID
    ,SUB.SUBMITTED_AT
    ,ELT.ELT
    ,COUNT(SUB.SUBMITTED_AT) AS WRONG_ANS1
FROM
    SUBMISSIONS AS SUB
JOIN
    EACH_LT AS ELT
ON
    SUB.USER_ID = ELT.USER_ID
AND
    SUB.PROBLEM_ID = ELT.PROBLEM_ID
WHERE
    ELT.ELT > SUB.SUBMITTED_AT
    AND CONTEST_ID = 2
    AND SUB.STATUS = 'WA'
    AND SUB.ENTRY_ID != 'NULL'
GROUP BY
    SUB.USER_ID
    ,SUB.PROBLEM_ID
)
,WRONG2 AS(
SELECT
    USER_ID
    ,SUM(WRONG_ANS1) AS WRONG_ANS
FROM
    WRONG
GROUP BY
    USER_ID
)

SELECT
    RANK() 
        OVER (ORDER BY PO.POINT DESC, EX.EX_TIME ASC) AS RANK
    ,EX.USER_ID
    ,PO.POINT
    ,EX.EX_TIME + 300*(CASE WHEN WG.WRONG_ANS != 'NULL' THEN WG.WRONG_ANS ELSE 0 END) AS EX_TIME
    ,CASE WHEN WG.WRONG_ANS != 'NULL' THEN WG.WRONG_ANS ELSE 0 END AS WRONG_ANS
FROM
    EXTIME AS EX
LEFT JOIN
    POINT AS PO
ON 
    EX.USER_ID = PO.USER_ID
LEFT JOIN
    WRONG2 AS WG
ON
    EX.USER_ID = WG.USER_ID
ORDER BY
    RANK ASC