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