with last_submitted_time as (
    select
        subm.CONTEST_ID,
        subm.ENTRY_ID,
        max(subm.SUBMITTED_AT) as LAST_SUBMITTED_TIME
    from
        SUBMISSIONS as subm
    where
        subm.STATUS = 'AC' and
        subm.ENTRY_ID is not null
    group by
        subm.CONTEST_ID,
        subm.ENTRY_ID
), cnt_wrong_ans as (
    select
        subm.CONTEST_ID,
        subm.ENTRY_ID,
        count(1) as WRONG_ANS
    from
        SUBMISSIONS as subm inner join last_submitted_time as lst
        on
            subm.CONTEST_ID = lst.CONTEST_ID and
            subm.ENTRY_ID = lst.ENTRY_ID
    where
        subm.SUBMITTED_AT < lst.LAST_SUBMITTED_TIME and
        subm.STATUS <> 'AC' and
        subm.ENTRY_ID is not null
    group by
        subm.CONTEST_ID,
        subm.ENTRY_ID
), point as (
    select
        subm.CONTEST_ID,
        subm.ENTRY_ID,
        entr.USER_ID,
        ifnull(cnt_wrong_ans.WRONG_ANS, 0) as WRONG_ANS,
        sum(subm.POINT) as POINT,
        max(strftime('%s', lst.LAST_SUBMITTED_TIME) - strftime('%s', entr.STARTED_AT) + 300 * ifnull(cnt_wrong_ans.WRONG_ANS, 0) ) as EX_TIME
    from
        SUBMISSIONS as subm inner join  last_submitted_time as lst
        on
            subm.CONTEST_ID = lst.CONTEST_ID and
            subm.ENTRY_ID = lst.ENTRY_ID
        inner join ENTRIES entr
        on
            subm.ENTRY_ID = entr.ENTRY_ID
        left outer join cnt_wrong_ans
        on
            subm.CONTEST_ID = cnt_wrong_ans.CONTEST_ID and
            subm.ENTRY_ID = cnt_wrong_ans.ENTRY_ID
    where
        subm.STATUS = 'AC' and
        subm.ENTRY_ID is not null
    group by
        subm.CONTEST_ID,
        subm.ENTRY_ID,
        entr.USER_ID
)
select
    rank() over (order by point.POINT desc, point.EX_TIME asc) as RANK,
    point.USER_ID,
    point.POINT,
    point.EX_TIME,
    point.WRONG_ANS
from
    point
where
    point.CONTEST_ID = '2' and
    point.POINT <> 0
order by RANK, point.WRONG_ANS, point.USER_ID