WITH PENA AS(
SELECT S.ENTRY_ID,
COUNT(SUBMIT_ID) AS WRONG
FROM SUBMISSIONS as S
INNER JOIN (
SELECT ENTRY_ID,
PROBLEM_ID,
MAX(SUBMITTED_AT) as LAST
FROM SUBMISSIONS
WHERE STATUS='AC'
and ENTRY_ID is not null
and CONTEST_ID=2
GROUP BY ENTRY_ID,PROBLEM_ID
)as SUB
ON S.ENTRY_ID=SUB.ENTRY_ID
and S.PROBLEM_ID=SUB.PROBLEM_ID
and S.SUBMITTED_AT<SUB.LAST
GROUP BY S.ENTRY_ID),
SCORE_TIME as (
SELECT ENTRY_ID,
SUM(POINT) as POINT,
MAX(SUBMITTED_AT) as LAST
FROM SUBMISSIONS as S
WHERE STATUS='AC'
and ENTRY_ID is not null
and CONTEST_ID=2
GROUP BY ENTRY_ID
)
SELECT RANK() OVER(
ORDER BY POINT DESC,
STRFTIME('%s',LAST)-STRFTIME('%s',STARTED_AT)+IFNULL(WRONG,0) ASC
)as RANK
,E.USER_ID
,POINT
,STRFTIME('%s',LAST)-STRFTIME('%s',STARTED_AT)+IFNULL(WRONG,0) as ans
FROM ENTRIES as e
LEFT OUTER JOIN PENA ON e.ENTRY_ID=PENA.ENTRY_ID
JOIN SCORE_TIME ON e.ENTRY_ID=SCORE_TIME.ENTRY_ID
WHERE CONTEST_ID=2
ORDER BY RANK ASC ,WRONG ASC , USER_ID ASC