WITH u_f AS(
SELECT
    s.USER_ID,
    s.PROBLEM_ID,
    min(s.SUBMITTED_AT) as first_aced
FROM SUBMISSIONS s
INNER JOIN ENTRIES e ON s.ENTRY_ID = e.ENTRY_ID
WHERE s.CONTEST_ID = 2 and s.STATUS = 'AC' AND s.ENTRY_ID is not null
GROUP BY s.USER_ID,s.PROBLEM_ID
),u_t AS(
SELECT
    s.USER_ID,
    s.PROBLEM_ID,
    sum(case 
        when s.STATUS = 'WA' and s.SUBMITTED_AT < u_f.first_aced then 1
        else 0
    end) as wrongs,
    max(case 
        when s.STATUS = 'AC' and s.SUBMITTED_AT <= u_f.first_aced then s.POINT
        else 0
    end) as POINT,
    max(
    case 
        when s.STATUS = 'AC' then strftime('%s', s.SUBMITTED_AT) - strftime('%s', e.STARTED_AT)
        else 0
    end) as EX_TIME
FROM SUBMISSIONS s
INNER JOIN ENTRIES e ON s.ENTRY_ID = e.ENTRY_ID
INNER JOIN u_f on u_f.USER_ID = s.USER_ID and u_f.PROBLEM_ID = s.PROBLEM_ID 
WHERE s.CONTEST_ID = 2
GROUP BY s.USER_ID,s.PROBLEM_ID),
u_s AS (
SELECT
    USER_ID,
    SUM(POINT) AS POINT,
    MAX(EX_TIME) AS EX_TIME,
    sum(case 
        when POINT > 0  then wrongs
        else 0
    end) as WRONG_ANS
FROM u_t
GROUP BY USER_ID)
SELECT
    RANK() OVER (
                ORDER BY
                    POINT DESC
                    , EX_TIME+300*WRONG_ANS
            ) AS RANK,
    USER_ID,
    POINT,
    EX_TIME+300*WRONG_ANS AS EX_TIME,
    WRONG_ANS
FROM u_s WHERE POINT >0
ORDER BY 
    RANK,
    WRONG_ANS,
    USER_ID