WITH AC_LAST AS (
   SELECT
      EN.USER_ID
     ,SB.SUBMITTED_AT
     ,RANK() OVER(PARTITION BY EN.USER_ID ORDER BY SB.SUBMITTED_AT DESC) AS RESULT
   FROM SUBMISSIONS SB
  INNER JOIN ENTRIES EN
     ON SB.ENTRY_ID = EN.ENTRY_ID
  WHERE EN.CONTEST_ID = '2'
    AND SB.STATUS = 'AC'
)
SELECT RANK() OVER(ORDER BY POINT DESC, EX_TIME, WRONG_ANS) AS RANK
      ,USER_ID
      ,POINT
      ,EX_TIME
      ,WRONG_ANS
FROM (
      SELECT EN.USER_ID AS USER_ID
            ,POINT.POINT  AS POINT
            ,(strftime('%s', LT.SUBMITTED_AT) - strftime('%s', EN.STARTED_AT)) + (300 * IFNULL(PENA.CNT, 0)) AS EX_TIME
            ,IFNULL(PENA.CNT, 0) AS WRONG_ANS
        FROM ENTRIES EN
       INNER JOIN
        (SELECT EN.USER_ID
               ,SUM(SB.POINT) AS POINT
           FROM  SUBMISSIONS SB
          INNER JOIN ENTRIES EN
             ON SB.ENTRY_ID = EN.ENTRY_ID
          INNER JOIN (SELECT USER_ID
                            ,SUBMITTED_AT
                        FROM AC_LAST
                       WHERE RESULT = 1) LT
             ON EN.USER_ID = LT.USER_ID
          WHERE EN.CONTEST_ID = '2'
            AND SB.STATUS = 'AC'
            AND EN.STARTED_AT <= SB.SUBMITTED_AT
            AND SB.SUBMITTED_AT <= LT.SUBMITTED_AT
         GROUP BY EN.USER_ID
        ) POINT
          ON EN.USER_ID = POINT.USER_ID
       INNER JOIN (SELECT USER_ID
                         ,SUBMITTED_AT
                     FROM AC_LAST
                    WHERE RESULT = 1) LT
          ON EN.USER_ID = LT.USER_ID
       LEFT OUTER JOIN
        (SELECT EN.USER_ID
               ,COUNT() AS CNT
           FROM SUBMISSIONS SB
          INNER JOIN ENTRIES EN
             ON SB.ENTRY_ID = EN.ENTRY_ID
          INNER JOIN (SELECT USER_ID
                            ,SUBMITTED_AT
                        FROM AC_LAST
                       WHERE RESULT = 1) LT
             ON EN.USER_ID = LT.USER_ID
          WHERE EN.CONTEST_ID = '2'
            AND SB.STATUS <> 'AC'
            AND EN.STARTED_AT <= SB.SUBMITTED_AT
            AND SB.SUBMITTED_AT <= LT.SUBMITTED_AT
            AND EXISTS (SELECT 1
                          FROM SUBMISSIONS SB2
                         INNER JOIN ENTRIES EN2
                            ON SB2.ENTRY_ID = EN2.ENTRY_ID 
                         WHERE SB2.CONTEST_ID = SB.CONTEST_ID
                           AND SB2.PROBLEM_ID = SB.PROBLEM_ID
                           AND SB2.USER_ID = SB.USER_ID
                           AND EN2.STARTED_AT <= SB2.SUBMITTED_AT
                           AND SB2.SUBMITTED_AT <= LT.SUBMITTED_AT
                           AND SB2.STATUS = 'AC')
          GROUP BY EN.USER_ID
        ) PENA
          ON EN.USER_ID = PENA.USER_ID
)
ORDER BY RANK, WRONG_ANS, USER_ID