select
    RANK() OVER(ORDER BY a.sp desc,(b.time + IFNULL(c.wa, 0) * 300)) RANK
    ,a.user_id USER_ID
    ,a.sp POINT
    ,(b.time + IFNULL(c.wa, 0) * 300) EX_TIME
    ,IFNULL(c.wa, 0) WRONG_ANS
from
    (
        -- 合計点数
        select
            s.USER_ID
            ,sum(s.POINT) sp
        from
            SUBMISSIONS s
        where
            s.CONTEST_ID = 2
        and s.ENTRY_ID is not null
        group by
            s.USER_ID
        having sp != 0
    ) a
    join
        (
            select
                s2.USER_ID
                ,max(strftime('%s', s2.SUBMITTED_AT) - strftime('%s', e2.STARTED_AT)) time
            from
                SUBMISSIONS s2
                join
                    ENTRIES e2
                on  s2.ENTRY_ID = e2.ENTRY_ID
            where
                s2.CONTEST_ID = 2
            and s2.ENTRY_ID is not null
            and s2.STATUS = 'AC'
            group by
                s2.user_id
        ) b
    on  a.user_id = b.user_id
    left outer join
        (
            -- 問題別誤答数
            select
                s3.USER_ID
                ,count(*) wa
            from
                SUBMISSIONS s3
            where
                s3.SUBMITTED_AT < (
                    select
                        max(s4.SUBMITTED_AT)
                    from
                        SUBMISSIONS s4
                    where
                        s4.status = 'AC'
                    and s4.CONTEST_ID = 2
                    and s4.ENTRY_ID is not null
                    and s3.USER_ID = s4.USER_ID
                    and s3.PROBLEM_ID = s4.PROBLEM_ID
                )
            group by
                s3.user_id
        ) c
    on  b.user_id = c.user_id
where a.sp != 0
order by rank, WRONG_ANS, a.user_id