with norm as (
    select
        E.USER_ID as USER_ID,
        S.SUBMIT_ID as SUBMIT_ID,
        S.PROBLEM_ID as PROBLEM_ID,
        unixepoch(S.SUBMITTED_AT) - unixepoch(E.STARTED_AT) as ELAPSED,
        S.STATUS as STATUS,
        S.POINT as POINT
    from
        ENTRIES as E,
        SUBMISSIONS as S
    where
        E.ENTRY_ID == S.ENTRY_ID
        and E.CONTEST_ID == 2
),
point_table as (
    select
        USER_ID,
        sum(POINT) as POINT
    from
        norm
    group by
        USER_ID
),
ac_time as (
    select
        USER_ID,
        PROBLEM_ID,
        max(
            case
                when STATUS == "AC" then ELAPSED
                else null
            end
        ) as AC_TIME
    from
        norm
    group by
        USER_ID,
        PROBLEM_ID
    having
        AC_TIME is not null
),
time_table as (
    select
        USER_ID,
        max(AC_TIME) as TIME
    from
        ac_time
    group by
        USER_ID
),
wa_table as (
    select
        A.USER_ID as USER_ID,
        count(*) as WA
    from
        ac_time as A,
        norm as N
    where
        A.USER_ID == N.USER_ID
        and A.PROBLEM_ID == N.PROBLEM_ID
        and N.ELAPSED < A.AC_TIME
    group by
        A.USER_ID
)
select
    rank() over(
        order by
            P.POINT desc,
            T.TIME + 300 * ifnull(W.WA, 0)
    ) as RANK,
    P.USER_ID as USER_ID,
    P.POINT as POINT,
    T.TIME + 300 * ifnull(W.WA, 0) as EX_TIME,
    ifnull(W.WA, 0) as WRONG_ANS
from
    point_table as P
    join time_table as T on P.USER_ID == T.USER_ID
    left outer join wa_table as W on P.USER_ID == W.USER_ID
order by
    RANK,
    WRONG_ANS,
    USER_ID