with penalty as (
    select
        s.ENTRY_ID,
        count(SUBMIT_ID) as WRONG_ANS
    from
        SUBMISSIONS s
        inner join (
            select
                ENTRY_ID,
                PROBLEM_ID,
                max(SUBMITTED_AT) as LAST_SUBMITTED_AT
            from
                SUBMISSIONS
            where
                STATUS = 'AC' 
                and 
                ENTRY_ID is not null
                and 
                CONTEST_ID = 2
            group by
                ENTRY_ID,
                PROBLEM_ID
        ) as SUB1
            on
                s.ENTRY_ID = SUB1.ENTRY_ID 
                and
                s.PROBLEM_ID = SUB1.PROBLEM_ID
                and
                s.SUBMITTED_AT < SUB1.LAST_SUBMITTED_AT 
    group by
        s.ENTRY_ID
),
score_and_time as (
    select
        ENTRY_ID,
        sum(POINT) as POINT,
        max(SUBMITTED_AT) as LAST_SUBMITTED_AT 
    from
        SUBMISSIONS s
    where
        STATUS = 'AC' 
        and
        ENTRY_ID is not null 
        and
        CONTEST_ID = 2 
    group by
        ENTRY_ID
)
select
    rank() over (
        order by
            POINT desc,
            STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 300 * ifnull(WRONG_ANS, 0)
    ) as RANK,
    e.USER_ID as USER_ID,
    POINT as POINT,
    STRFTIME('%s', LAST_SUBMITTED_AT) - STRFTIME('%s', STARTED_AT) + 300 * ifnull(WRONG_ANS, 0) as EX_TIME,
    ifnull(WRONG_ANS, 0) as WRONG_ANS
from
    ENTRIES e
    left outer join PENALTY p
        on p.ENTRY_ID = e.ENTRY_ID
    join SCORE_AND_TIME sat
        on e.ENTRY_ID = sat.ENTRY_ID
where
    e.CONTEST_ID = 2
order by
    RANK,
    WRONG_ANS,
    USER_ID;