----------
with subq as(
select CONTEST_ID
        ,USER_ID
        ,max(SUBMITTED_AT) as lasttime
        from SUBMISSIONS 
        where STATUS = 'AC'
group by CONTEST_ID
        ,USER_ID
)
, time as(
select A.CONTEST_ID
        ,A.USER_ID
        ,STARTED_AT as starttime
        ,lasttime
        ,strftime('%s', lasttime) - strftime('%s', STARTED_AT) as time
from subq A
left outer join ENTRIES B
on A.CONTEST_ID = B.CONTEST_ID
and A.USER_ID = B.USER_ID
)
, point as (
select  CONTEST_ID
        ,USER_ID
        ,sum(POINT) as point 
        from(
select distinct 
        CONTEST_ID
        ,USER_ID
        ,PROBLEM_ID
        ,POINT
        from SUBMISSIONS )
        group by CONTEST_ID
        ,USER_ID

)
, WA_count as (
select CONTEST_ID
        ,USER_ID
        ,sum(case when STATUS = 'WA' then 1 else 0 end) as WA_count
        from SUBMISSIONS 
group by CONTEST_ID
        ,USER_ID
)
, summary as(
select A.USER_ID 
	,point as POINT
	,time + (C.WA_count * 300) as EX_TIME
	,C.WA_count as WRONG_ANS
from point as A
left outer join time as B
on A.CONTEST_ID = B.CONTEST_ID
and A.USER_ID = B.USER_ID
left outer join WA_count as C
on A.CONTEST_ID = C.CONTEST_ID
and A.USER_ID = C.USER_ID
where A.CONTEST_ID = 2
and point > 0 
)
select row_number() over (order by POINT desc,EX_TIME asc) as RANK
    ,*
    from summary