with ac_time as (
    select
        min(SUBMISSIONS.SUBMITTED_AT) as "ac_time",
        SUBMISSIONS.PROBLEM_ID,
        SUBMISSIONS.USER_ID
    from
        SUBMISSIONS
    where
        SUBMISSIONS.ENTRY_ID is not null
        and SUBMISSIONS.STATUS = 'AC'
        and SUBMISSIONS.CONTEST_ID = 2
    group by
        SUBMISSIONS.USER_ID,
        SUBMISSIONS.PROBLEM_ID
),
sub as (
    select
        SUBMISSIONS.ENTRY_ID,
        SUBMISSIONS.USER_ID,
        sum(SUBMISSIONS.POINT) as POINT_,
        max(ac_time.ac_time) as "ac_time",
        count(
            case
                when SUBMISSIONS.STATUS <> 'AC' then 1
            end
        ) as "wrong_ans"
    from
        SUBMISSIONS
        join ac_time on ac_time.PROBLEM_ID = SUBMISSIONS.PROBLEM_ID
        and ac_time.USER_ID = SUBMISSIONS.USER_ID
        and SUBMISSIONS.SUBMITTED_AT <= ac_time.ac_time
    where
        SUBMISSIONS.CONTEST_ID = 2
        and SUBMISSIONS.ENTRY_ID is not null
    group by
        SUBMISSIONS.ENTRY_ID,
        SUBMISSIONS.USER_ID
    having
        POINT_ > 0
)
select
    rank() over(
        order by
            sub.POINT_ desc,
            unixepoch(sub.ac_time) - unixepoch(ENTRIES.STARTED_AT) + sub.wrong_ans * 300
    ) as "RANK",
    ENTRIES.USER_ID as "USER_ID",
    sub.POINT_ as "POINT",
    unixepoch(sub.ac_time) - unixepoch(ENTRIES.STARTED_AT) + sub.wrong_ans * 300 as "EX_TIME",
    sub.wrong_ans as "WRONG_ANS"
from
    ENTRIES
    join sub on sub.ENTRY_ID = ENTRIES.ENTRY_ID
where
    ENTRIES.CONTEST_ID = 2
order by
    RANK,
    WRONG_ANS,
    USER_ID;