WITH LAC AS (
    SELECT
        ENTRY_ID,
        MAX(SUBMITTED_AT) SUBMITTED_AT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID=2 AND STATUS = 'AC'
    GROUP BY
        ENTRY_ID
)
, 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
),
D AS (
SELECT
    E.USER_ID AS USER_ID,
    SUM(S.POINT) AS POINT ,
    CASE WHEN LAC.SUBMITTED_AT IS NULL THEN 0 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
        AND E.CONTEST_ID=S.CONTEST_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
)
SELECT
    RANK() OVER ( ORDER BY POINT DESC, EX_TIME ASC ) AS RANK,
    USER_ID,
    POINT ,
    EX_TIME,
    WRONG_ANS
FROM
    D
ORDER BY
    1 ASC,
    3 DESC,
    5 ASC