with base as (
select
SUBMISSIONS.user_id
,submit_id
,submitted_at
,problem_id
,status
,point
,started_at
from SUBMISSIONS
inner join ENTRIES on SUBMISSIONS.entry_id = ENTRIES.entry_id
where SUBMISSIONS.contest_id = 2
and SUBMISSIONS.entry_id is not null 
and (strftime('%s', submitted_at) - strftime('%s', started_at)) < 60 * 60
-- and SUBMISSIONS.user_id = 64
)
,first_submit as (select
user_id
,problem_id
,min(SUBMITTED_AT) as first_submit
,max(SUBMITTED_AT) as max_submit
from base
where status = 'AC'
group by 1,2
)
-- select
-- base.*
-- -- ,first_submit
-- -- ,max_submit
-- from base
-- left join first_submit 
--     on base.user_id = first_submit.user_id
--     and base.problem_id = first_submit.problem_id
,summary as (
select
base.user_id
,started_at
,sum(case when first_submit is null or submitted_at < first_submit then 1 else 0 end) as count_wa
,max(max_submit) as last_submit
,sum(point) as sum_point
from base
inner join first_submit 
    on base.user_id = first_submit.user_id
    and base.problem_id = first_submit.problem_id
group by 1,2
)
select
rank() over (order by POINT desc, EX_TIME asc) as RANK
,*
from (
select
user_id as USER_ID
,sum_point as POINT
,(strftime('%s', last_submit) - strftime('%s', started_at)) + 300 * count_wa as EX_TIME
, count_wa as WRONG_ANS
from summary
) tbl
where POINT != 0
order by RANK, WRONG_ANS , USER_ID