select
   RANK () OVER ( ORDER BY TMP.POINT DESC ,TMP.EX_TIME asc) as RANK,
TMP.*

    from
(
select E.USER_ID,
       PO.POINT,
       LAST_SUB.SUB_AT + COALESCE(WA.WACNT, 0) * 300 as EX_TIME,
       COALESCE(WA.WACNT, 0)                         as WRONG_ANS

from ENTRIES E
         inner join(select E.ENTRY_ID, sum(POINT) POINT
                    from SUBMISSIONS SUB
                             inner join ENTRIES E
                                        on SUB.ENTRY_ID = E.ENTRY_ID

                    where SUB.STATUS = 'AC'

                    group by E.ENTRY_ID) PO on PO.ENTRY_ID = E.ENTRY_ID


         inner join (select E.ENTRY_ID,
                            max(strftime('%s', coalesce(SUB.SUBMITTED_AT, '2100-01-01 00:00:00')) -
                                strftime('%s', E.STARTED_AT)) as SUB_AT
                     from SUBMISSIONS SUB
                              inner join ENTRIES E
                                         on SUB.ENTRY_ID = E.ENTRY_ID

                     where E.CONTEST_ID = 2
                       and SUB.STATUS = 'AC'
                     group by E.ENTRY_ID) LAST_SUB
                    on LAST_SUB.ENTRY_ID = E.ENTRY_ID

         left join (select E.ENTRY_ID, count(*) WACNT
                    from SUBMISSIONS SUB
                             inner join ENTRIES E
                                        on SUB.ENTRY_ID = E.ENTRY_ID
                             LEFT OUTER JOIN
                         (select SUB.ENTRY_ID,
                                 SUB.PROBLEM_ID,
                                 strftime('%s', coalesce(SUB.SUBMITTED_AT, '2100-01-01 00:00:00')) as actime
                          from SUBMISSIONS SUB
                          where SUB.CONTEST_ID = 2
                            and SUB.STATUS = 'AC') AC_TIME
                         ON AC_TIME.ENTRY_ID = SUB.ENTRY_ID
                             AND AC_TIME.PROBLEM_ID = SUB.PROBLEM_ID
                    where E.CONTEST_ID = 2
                      and SUB.STATUS = 'WA'
                      and (AC_TIME.ENTRY_ID is null or
                           strftime('%s', AC_TIME.actime) < strftime('%s', SUB.SUBMITTED_AT))
                    group by E.ENTRY_ID) WA
                   on E.ENTRY_ID = WA.ENTRY_ID
where E.CONTEST_ID = 2
) TMP

order by 1,5,2;