with v1 as
(
    select
        ENT.USER_ID as USER_ID
    ,   SUB.POINT as POINT
    ,   SUB.PROBLEM_ID as PROB
    ,   STATUS as STATUS
    ,   strftime('%s', ENT.STARTED_AT) as START
    ,   strftime('%s', SUB.SUBMITTED_AT) as SUBMIT
    from
        SUBMISSIONS SUB
    join
        ENTRIES ENT
    on
        ENT.ENTRY_ID = SUB.ENTRY_ID
    and ENT.CONTEST_ID = SUB.CONTEST_ID
    and ENT.USER_ID = SUB.USER_ID
    where
        SUB.CONTEST_ID = '2'
)

select
    RANK() OVER(ORDER BY t1.POINT DESC, (t1.TOTAL_TIME + t2.CNT * 300)) as RANK
,   t1.USER_ID as USER_ID
,   t1.POINT as POINT
,   t1.TOTAL_TIME + t2.CNT * 300 as EX_TIME
,   t2.CNT as WRONG_ANS
from
    (
        select
            v1.USER_ID as USER_ID
        ,   sum(v1.POINT) as POINT
        ,   max(v1.SUBMIT) - v1.START as TOTAL_TIME
        from
            v1
        group by
            v1.USER_ID
    ) t1
join
    (
        select
            t3.USER_ID as USER_ID
        ,   max(t3.CNT) as CNT
        from
            (
                select
                    t4.USER_ID as USER_ID
                ,   sum(t4.CNT) as CNT
                from
                    (
                        select
                            t31.USER_ID as USER_ID
                        ,   t31.PROB as PROB
                        ,   count(t31.WRONG_SUB) as CNT
                        from
                            (
                                select
                                    v1.USER_ID as USER_ID
                                ,   v1.PROB as PROB
                                ,   v1.SUBMIT as WRONG_SUB
                                from
                                    v1
                                where
                                    v1.STATUS = 'WA'
                            ) t31
                        join
                            (
                                select
                                    v1.USER_ID as USER_ID
                                ,   v1.PROB as PROB
                                ,   max(v1.SUBMIT) as LAST_AC
                                from
                                    v1
                                where
                                    v1.STATUS = 'AC'
                                group by
                                    v1.USER_ID
                                ,   v1.PROB
                            ) t32
                        on
                            t31.USER_ID = t32.USER_ID
                        and t31.PROB = t32.PROB
                        where
                            t31.WRONG_SUB < t32.LAST_AC
                        group by
                            t31.USER_ID
                        ,   t31.PROB
                    ) t4
                group by
                    t4.USER_ID

                union
        
                select 
                    v1.USER_ID as USER_ID
                ,   0 as CNT
                from
                    v1
            ) t3
        group by
            t3.USER_ID
    ) t2
on
    t1.USER_ID = t2.USER_ID
where
    POINT > 0
order by
    RANK
,   USER_ID