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