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 
        ,sum(case when STATUS = 'WA' then 1 else 0 end) as WA_count
        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