SELECT RANK() OVER(ORDER BY POINT DESC,TIME_SUM) AS RANK
,USER_ID AS USER_ID
,POINT AS POINT
,TIME_SUM EX_TIME
,MISS WRONG_ANS

FROM
(SELECT A.USER_ID USER_ID
,SUM(A.POINT) POINT
,COALESCE(SUM(TIME_SUM + 300*MISS),TIME_SUM) TIME_SUM
,COALESCE(SUM(MISS),0) MISS
FROM
(SELECT ENTRIES.CONTEST_ID CONTEST_ID
,ENTRIES.ENTRY_ID ENTRY_ID
,ENTRIES.USER_ID USER_ID
,PROBLEM_ID

,strftime('%s',SUBMITTED_AT)-strftime('%s',STARTED_AT) TIME_SUM
,STATUS
,POINT
FROM ENTRIES
LEFT JOIN SUBMISSIONS
ON ENTRIES.ENTRY_ID  = SUBMISSIONS.ENTRY_ID 
WHERE ENTRIES.CONTEST_ID = 2
AND STATUS = 'AC'
AND strftime('%s',SUBMITTED_AT)-strftime('%s',STARTED_AT)>0
AND ENTRIES.ENTRY_ID IS NOT NULL) A
LEFT JOIN
(SELECT ENTRIES.CONTEST_ID CONTEST_ID
,ENTRIES.ENTRY_ID ENTRY_ID
,ENTRIES.USER_ID USER_ID
,PROBLEM_ID

,COUNT(*) MISS
FROM ENTRIES
LEFT JOIN SUBMISSIONS
ON ENTRIES.ENTRY_ID  = SUBMISSIONS.ENTRY_ID 
LEFT JOIN
(SELECT ENTRY_ID,SUBMITTED_AT
FROM SUBMISSIONS
WHERE CONTEST_ID = 2
AND STATUS = 'AC') C
ON SUBMISSIONS.ENTRY_ID = C.ENTRY_ID
WHERE ENTRIES.CONTEST_ID = 2
AND STATUS <> 'AC'
AND C.SUBMITTED_AT>SUBMISSIONS.SUBMITTED_AT
AND ENTRIES.ENTRY_ID IS NOT NULL
GROUP BY ENTRIES.ENTRY_ID,ENTRIES.USER_ID
,PROBLEM_ID) B
ON A.ENTRY_ID = B.ENTRY_ID

GROUP BY A.USER_ID
HAVING SUM(POINT)>0)

ORDER BY RANK,WRONG_ANS,USER_ID