WITH LAC AS (
    SELECT
        ENTRY_ID,
        MAX(SUBMITTED_AT) SUBMITTED_AT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID=2 AND STATUS = 'AC'
    GROUP BY
        ENTRY_ID
    HAVING
        MAX(SUBMITTED_AT)
)
, WA AS (
    SELECT
        S.ENTRY_ID,
        COUNT() AS COUNT
    FROM
        SUBMISSIONS S
        LEFT OUTER JOIN
        LAC
        ON
            S.ENTRY_ID = LAC.ENTRY_ID
    WHERE
        S.CONTEST_ID=2 AND S.SUBMITTED_AT < LAC.SUBMITTED_AT
        AND STATUS = 'WA'
    GROUP BY
        S.ENTRY_ID
)
SELECT
    RANK() OVER ( ORDER BY SUM(S.POINT) DESC, CASE WHEN LAC.SUBMITTED_AT IS NULL THEN -1 ELSE strftime('%s',LAC.SUBMITTED_AT) - strftime('%s',E.STARTED_AT) END
    - CASE WHEN WA.COUNT IS NULL THEN 0 ELSE WA.COUNT * 300 END ASC ) AS RANK,
    E.USER_ID AS USER_ID,
    SUM(S.POINT) AS POINT,
    CASE WHEN LAC.SUBMITTED_AT IS NULL THEN -1 ELSE strftime('%s',LAC.SUBMITTED_AT) - strftime('%s',E.STARTED_AT) END
    - CASE WHEN WA.COUNT IS NULL THEN 0 ELSE WA.COUNT * 300 END AS EX_TIME,
    SUM(CASE WHEN STATUS='WA' THEN 1 ELSE 0 END) AS WRONG_ANS
FROM
    ENTRIES E
    LEFT OUTER JOIN
        SUBMISSIONS S
    ON
        E.ENTRY_ID = S.ENTRY_ID
    LEFT OUTER JOIN
    LAC
    ON
        E.ENTRY_ID = LAC.ENTRY_ID
    LEFT OUTER JOIN
    WA
    ON
        E.ENTRY_ID = WA.ENTRY_ID
WHERE
    E.CONTEST_ID=2
GROUP BY
    E.USER_ID,
    E.ENTRY_ID
HAVING
    SUM(POINT)>0
ORDER BY
    WRONG_ANS ASC,
    USER_ID ASC