with last_submitted_time as (
            select
                subm.CONTEST_ID,
                subm.ENTRY_ID,
                subm.USER_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,
                subm.USER_ID
        ), cnt_wrong_ans as (
            select
                subm.CONTEST_ID,
                subm.ENTRY_ID,
                subm.USER_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 and
                    subm.USER_ID = lst.USER_ID
            where
                subm.SUBMITTED_AT < lst.LAST_SUBMITTED_TIME and
                subm.STATUS = 'WA'
            group by
                subm.CONTEST_ID,
                subm.ENTRY_ID,
                subm.USER_ID
        ), point as (
            select
                subm.CONTEST_ID,
                subm.ENTRY_ID,
                subm.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.USER_ID = lst.USER_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 and
                    subm.USER_ID = cnt_wrong_ans.USER_ID
            where
                subm.STATUS = 'AC'
            group by
                subm.CONTEST_ID,
                subm.ENTRY_ID,
                subm.USER_ID
        )
        select
            dense_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'
        order by RANK, WRONG_ANS, point.USER_ID