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,
            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
                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 < 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(subm.POINT) 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'
    order by RANK, WRONG_ANS, USER_ID