WITH SUB AS (SELECT PROBLEM_ID,
    USER_ID,
    POINT,
    SUBMITTED_AT
FROM SUBMISSIONS S1
WHERE CONTEST_ID = 2
AND ENTRY_ID IS NOT NULL
AND EXISTS (
    SELECT 1 
    FROM SUBMISSIONS S2
    WHERE S2.USER_ID = S1.USER_ID
    AND S2.PROBLEM_ID = S1.PROBLEM_ID
    AND S2.STATUS = 'AC'
    AND S2.ENTRY_ID IS NOT NULL
    AND S2.SUBMITTED_AT >= S1.SUBMITTED_AT
)
ORDER BY USER_ID, PROBLEM_ID )
, SUB2 AS (
SELECT 
    SUB.USER_ID,
    SUM(POINT) POINT,
    strftime('%s', MAX(SUBMITTED_AT)) - strftime('%s', STARTED_AT) + 300*COUNT(CASE WHEN SUB.POINT=0 THEN 1 ELSE NULL END) EX_TIME,
    COUNT(CASE WHEN SUB.POINT=0 THEN 1 ELSE NULL END) WRONG_ANS
    
FROM SUB
INNER JOIN ENTRIES E
    ON E.USER_ID = SUB.USER_ID AND E.CONTEST_ID = 2
GROUP BY SUB.USER_ID 
HAVING SUM(POINT) > 0 )


SELECT RANK() OVER(ORDER BY POINT DESC, EX_TIME ) RANK,
    USER_ID,
    POINT,
    EX_TIME,
    WRONG_ANS
FROM SUB2
ORDER BY POINT DESC, EX_TIME, USER_ID