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