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 - strftime('%s', E.STARTED_AT) + (COALESCE(WA.WACNT, 0) * 300) as EX_TIME,
             COALESCE(WA.WACNT, 0)                                                        as WRONG_ANS

      from ENTRIES E
               inner join(select SUB.ENTRY_ID, sum(POINT) POINT
                          from SUBMISSIONS SUB
                          where SUB.STATUS = 'AC'
                          group by SUB.ENTRY_ID) PO on PO.ENTRY_ID = E.ENTRY_ID

               inner join (select SUB.ENTRY_ID,
                                  max(strftime('%s', SUB.SUBMITTED_AT)) as SUB_AT
                           from SUBMISSIONS SUB
                           where SUB.STATUS = 'AC'
                           group by SUB.ENTRY_ID) LAST_SUB
                          on LAST_SUB.ENTRY_ID = E.ENTRY_ID

               left join (select SUB.ENTRY_ID, count(*) WACNT
                          from SUBMISSIONS SUB
                                   INNER JOIN
                               (select SUB.ENTRY_ID,
                                       SUB.PROBLEM_ID,
                                       SUB.SUBMITTED_AT
                                from SUBMISSIONS SUB
                                where SUB.STATUS = 'AC') AC_TIME
                               ON AC_TIME.ENTRY_ID = SUB.ENTRY_ID
                                   AND AC_TIME.PROBLEM_ID = SUB.PROBLEM_ID
                          where SUB.STATUS <> 'AC'
                            and strftime('%s', AC_TIME.SUBMITTED_AT) > strftime('%s', SUB.SUBMITTED_AT)
                          group by SUB.ENTRY_ID) WA
                         on E.ENTRY_ID = WA.ENTRY_ID
      where E.CONTEST_ID = 2) TMP

order by 1, 5, 2;