with penalty as (
    select
        e.ENTRY_ID as ENTRY_ID,
        sum(
            case when s.SUBMITTED_AT < (
                select max(s2.SUBMITTED_AT)
                from SUBMISSIONS s2
                where s2.ENTRY_ID = s.ENTRY_ID and s2.PROBLEM_ID = s.PROBLEM_ID and s2.CONTEST_ID = s.CONTEST_ID and s2.STATUS = 'AC' 
            )
            then 1
            else 0
            end
        ) as WRONG_ANS
    from ENTRIES e
    inner join
        SUBMISSIONS s
        on
            s.ENTRY_ID = e.ENTRY_ID
            and
            s.CONTEST_ID = e.CONTEST_ID
    where
        e.CONTEST_ID = 2
    group by
        e.ENTRY_ID
),
score_and_time as (
    select
        e.ENTRY_ID as ENTRY_ID,
        e.USER_ID as USER_ID,
        sum(s.POINT) as POINT,
        max(s.SUBMITTED_AT) as LAST_SUBMITTED_AT,
        e.STARTED_AT as STARTED_AT
    from ENTRIES e
    inner join
        SUBMISSIONS s
        on 
            s.ENTRY_ID = e.ENTRY_ID
            and
            s.CONTEST_ID = e.CONTEST_ID
    where
        e.CONTEST_ID = 2
        and
        s.STATUS = 'AC'
        and
        s.ENTRY_ID is not null
    group by
        e.ENTRY_ID
)
select
    rank() over (order by score_and_time.POINT desc, STRFTIME('%s', score_and_time.LAST_SUBMITTED_AT) - STRFTIME('%s', score_and_time.STARTED_AT) + (300 * penalty.WRONG_ANS)) as RANK,
    score_and_time.USER_ID as USER_ID,
    score_and_time.POINT as POINT,
    STRFTIME('%s', score_and_time.LAST_SUBMITTED_AT) - STRFTIME('%s', score_and_time.STARTED_AT) + (300 * penalty.WRONG_ANS) as EX_TIME,
    penalty.WRONG_ANS as WRONG_ANS
from
    penalty
inner join
    score_and_time
    on score_and_time.ENTRY_ID = penalty.ENTRY_ID
group by penalty.ENTRY_ID
having score_and_time.POINT <> 0
order by
    RANK,
    WRONG_ANS,
    USER_ID;