WITH CALC_TEMP1 AS
     (
         SELECT
              MAIN.USER_ID                                                                                   AS USER_ID
             ,MIN(MAIN.STARTED_AT)                                                                           AS STARTED_AT
             ,SUM(SUB.POINT)                                                                                 AS POINT
             ,SUM(CASE WHEN SUB.STATUS != 'AC' AND SUB.SUBMITTED_AT < MAX_SUB.MAX_AC_TIME THEN 1 ELSE 0 END) AS WRONG_ANS
             ,MAX(MAX_SUB.MAX_AC_TIME)                                                                       AS MAX_AC_TIME
         FROM ENTRIES AS MAIN
              INNER JOIN SUBMISSIONS AS SUB
                  ON MAIN.ENTRY_ID   = SUB.ENTRY_ID
                 AND MAIN.CONTEST_ID = SUB.CONTEST_ID
              INNER JOIN (SELECT CONTEST_ID, ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS MAX_AC_TIME FROM SUBMISSIONS WHERE STATUS = 'AC' GROUP BY CONTEST_ID, ENTRY_ID, PROBLEM_ID) AS MAX_SUB
                  ON SUB.ENTRY_ID    = MAX_SUB.ENTRY_ID
                 AND SUB.CONTEST_ID  = MAX_SUB.CONTEST_ID
                 AND SUB.PROBLEM_ID  = MAX_SUB.PROBLEM_ID
         WHERE MAIN.CONTEST_ID = 2
         GROUP BY MAIN.USER_ID
     )
    ,CLAC_TEMP2 AS
     (
         SELECT
              USER_ID
             ,POINT
             ,strftime('%s', MAX_AC_TIME) - strftime('%s', STARTED_AT) + (300 * WRONG_ANS) AS EX_TIME
             ,WRONG_ANS
         FROM CALC_TEMP1
     )
SELECT
     RANK() OVER(ORDER BY POINT DESC, EX_TIME) AS RANK
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
FROM CLAC_TEMP2
WHERE POINT > 0
ORDER BY RANK, WRONG_ANS, USER_ID
;
WITH CALC_TEMP1 AS
     (
         SELECT
              MAIN.USER_ID                                                                                   AS USER_ID
             ,MIN(MAIN.STARTED_AT)                                                                           AS STARTED_AT
             ,SUM(SUB.POINT)                                                                                 AS POINT
             ,SUM(CASE WHEN SUB.STATUS != 'AC' AND SUB.SUBMITTED_AT < MAX_SUB.MAX_AC_TIME THEN 1 ELSE 0 END) AS WRONG_ANS
             ,MAX(MAX_SUB.MAX_AC_TIME)                                                                       AS MAX_AC_TIME
         FROM ENTRIES AS MAIN
              INNER JOIN SUBMISSIONS AS SUB
                  ON MAIN.ENTRY_ID  = SUB.ENTRY_ID
                 AND SUB.CONTEST_ID = SUB.CONTEST_ID
              INNER JOIN (SELECT CONTEST_ID, ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) AS MAX_AC_TIME FROM SUBMISSIONS WHERE STATUS = 'AC' GROUP BY CONTEST_ID, ENTRY_ID, PROBLEM_ID) AS MAX_SUB
                  ON SUB.ENTRY_ID   = MAX_SUB.ENTRY_ID
                 AND SUB.CONTEST_ID = MAX_SUB.CONTEST_ID
                 AND SUB.PROBLEM_ID = MAX_SUB.PROBLEM_ID
         WHERE MAIN.CONTEST_ID = 2
         GROUP BY MAIN.USER_ID
     )
    ,CLAC_TEMP2 AS
     (
         SELECT
              USER_ID
             ,POINT
             ,strftime('%s', MAX_AC_TIME) - strftime('%s', STARTED_AT) + (300 * WRONG_ANS) AS EX_TIME
             ,WRONG_ANS
         FROM CALC_TEMP1
     )
SELECT
     RANK() OVER(ORDER BY POINT DESC, EX_TIME) AS RANK
    ,USER_ID
    ,POINT
    ,EX_TIME
    ,WRONG_ANS
FROM CLAC_TEMP2
WHERE POINT > 0
ORDER BY RANK, WRONG_ANS, USER_ID
;