with t1 as
(
select
    *
from
    SUBMISSIONS left join ENTRIES
    using(CONTEST_ID, USER_ID, ENTRY_ID)
where
    CONTEST_ID = 2
    and ENTRY_ID is not null
),
t_max as
(
select
    PROBLEM_ID
    ,USER_ID
    ,STATUS
    ,max(SUBMITTED_AT) as SUBMITTED_AT
from
    t1
where
    STATUS = 'AC'
group by
    PROBLEM_ID
    ,USER_ID
    ,STATUS
), t2 as
(
select
    *
from
    t1
where
    exists(
        select
            *
        from
            t_max
        where
            t1.PROBLEM_ID = t_max.PROBLEM_ID 
            and t1.USER_ID = t_max.USER_ID 
            and t1.SUBMITTED_AT <= t_max.SUBMITTED_AT
    )
    -- and ENTRY_ID is not null
order by
    USER_ID, PROBLEM_ID
)



select
    rank() over(order by sum(POINT) desc,(max(time) + sum(WA_cnt)*300) asc) as RANK
    ,USER_ID
    ,sum(POINT) as POINT
    ,max(time) + sum(WA_cnt)*300 as EX_TIME
    ,sum(WA_cnt) as WRONG_ANS
from
(
select
    PROBLEM_ID, USER_ID
    ,sum(POINT) as POINT
    ,sum(case when STATUS = 'AC' then 1 else 0 end) as AC_cnt
    ,sum(case when STATUS <> 'AC' then 1 else 0 end) as WA_cnt
    ,max(case when STATUS = 'AC' then strftime('%s',SUBMITTED_AT) - strftime('%s',STARTED_AT) else 0 end) as time
from
    t2
group by
    PROBLEM_ID, USER_ID
)
group by
    USER_ID
order by
    POINT desc, EX_TIME asc