WITH LPAC AS (
    SELECT
        ENTRY_ID,
        CONTEST_ID,
        PROBLEM_ID,
        MAX(SUBMITTED_AT) SUBMITTED_AT
    FROM
        SUBMISSIONS
    WHERE
        CONTEST_ID=2 AND STATUS = 'AC'
    GROUP BY
        ENTRY_ID
    HAVING
        MAX(SUBMITTED_AT)
)
, PWA AS (
    SELECT
        S.ENTRY_ID,
        COUNT() AS COUNT
    FROM
        SUBMISSIONS S
        LEFT OUTER JOIN
        LPAC
        ON
            S.ENTRY_ID = LPAC.ENTRY_ID
            AND S.CONTEST_ID = LPAC.CONTEST_ID
            AND S.PROBLEM_ID = LPAC.PROBLEM_ID
    WHERE
        S.CONTEST_ID=2 AND S.SUBMITTED_AT < LPAC.SUBMITTED_AT
        AND STATUS = 'WA'
    GROUP BY
        S.ENTRY_ID
),
LAC AS (
    SELECT
        ENTRY_ID,
        MAX(SUBMITTED_AT) AS SUBMITTED_AT
    FROM
        LPAC
    GROUP BY
        ENTRY_ID
)
, WA AS (
    SELECT
        PWA.ENTRY_ID,
        SUM(COUNT) AS COUNT
    FROM
        PWA
    GROUP BY
        PWA.ENTRY_ID
)
SELECT
    RANK() OVER ( ORDER BY SUM(S.POINT) DESC, 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 ASC ) AS RANK,
    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
    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
    1 ASC,
    3 DESC,
    5 ASC