WITH ex_time AS (
    SELECT e.ENTRY_ID, strftime('%s', MAX_SUBMITTED_AT) - strftime('%s', STARTED_AT) AS EX_TIME
    FROM ENTRIES e
    JOIN (
        SELECT ENTRY_ID, MAX(SUBMITTED_AT) MAX_SUBMITTED_AT
        FROM SUBMISSIONS
        WHERE STATUS='AC' AND ENTRY_ID IS NOT NULL
        GROUP BY ENTRY_ID
    ) s ON e.ENTRY_ID=s.ENTRY_ID
    WHERE e.CONTEST_ID=2
), submission_ac_time AS (
    SELECT s.ENTRY_ID, s.PROBLEM_ID, COUNT(DISTINCT s.SUBMIT_ID) AS WRONG_ANS
    FROM SUBMISSIONS s
    JOIN (
        SELECT e.ENTRY_ID, PROBLEM_ID, MAX(SUBMITTED_AT) SUBMITTED_AT
        FROM ENTRIES e
        JOIN SUBMISSIONS s ON e.ENTRY_ID=s.ENTRY_ID AND e.CONTEST_ID=s.CONTEST_ID AND e.USER_ID=s.USER_ID
        WHERE e.CONTEST_ID=2 AND STATUS='AC'
    ) j ON s.ENTRY_ID=j.ENTRY_ID AND s.PROBLEM_ID=j.PROBLEM_ID
    WHERE s.STATUS<>'AC' AND s.SUBMITTED_AT <= j.SUBMITTED_AT
), points AS (
    SELECT e.ENTRY_ID, e.USER_ID, SUM(POINT) AS POINT
    FROM ENTRIES e
    JOIN SUBMISSIONS s ON e.ENTRY_ID = s.ENTRY_ID
    GROUP BY e.ENTRY_ID, e.USER_ID
)

SELECT RANK() OVER(ORDER BY p.POINT DESC, e.EX_TIME) AS RANK, p.USER_ID, p.POINT, e.EX_TIME, coalesce(s.WRONG_ANS, 0) AS WRONG_ANS
FROM points p
LEFT JOIN ex_time e ON p.ENTRY_ID=e.ENTRY_ID
LEFT JOIN submission_ac_time s ON p.ENTRY_ID=s.ENTRY_ID 
WHERE p.POINT <> 0
ORDER BY RANK ASC, WRONG_ANS ASC, USER_ID ASC