with tmp as
(select
SUBMISSIONS.USER_ID,SUBMISSIONS.PROBLEM_ID,SUBMISSIONS.STATUS,SUBMISSIONS.POINT,ENTRIES.STARTED_AT,SUBMISSIONS.SUBMITTED_AT,
(unixepoch(SUBMISSIONS.SUBMITTED_AT) -unixepoch(ENTRIES.STARTED_AT)) as EX_TIME
from 
SUBMISSIONS
inner join ENTRIES
on 
SUBMISSIONS.CONTEST_ID = ENTRIES.CONTEST_ID
and SUBMISSIONS.USER_ID = ENTRIES.USER_ID
and SUBMISSIONS.CONTEST_ID = 2
where SUBMISSIONS.ENTRY_ID not null
)select RANK() over (order by "POINT" desc,"EX_TIME" asc) as "RANK",tmp3.* from
(
select 
tmp2."USER_ID", sum(tmp2."POINT") as "POINT", 
case when tmp2."WA" NOT NULL then MAX(tmp2."EX_TIME") + 300 * tmp2."WA"
else MAX(tmp2."EX_TIME")
end as "EX_TIME",
case when tmp2."WA" NOT NULL then tmp2."WA"
else 0
end as "WRONG_ANS"
from
(
select tt1.*,tt2."WA" from (select t1.* from (select * from tmp) as t1 where t1."STATUS" = 'AC') as tt1
left join 
(select tt22.USER_ID,count(tt22.WRONG_ANS) as "WA" from
(select t2.USER_ID, t2.PROBLEM_ID, t2.STARTED_AT,t2.SUBMITTED_AT,count(*) over (partition by t2.USER_ID, t2.PROBLEM_ID) as WRONG_ANS from (select * from tmp) as t2 where t2.STATUS != 'AC' 
and t2.SUBMITTED_AT < (select 
case when exists(select * from (select * from tmp) as t4 where t4.USER_ID = t2.USER_ID and t4.PROBLEM_ID = t2.PROBLEM_ID and t4.STATUS = 'AC' limit 1)
then (select t3.SUBMITTED_AT from (select * from tmp) as t3 where t3.USER_ID = t2.USER_ID and t3.PROBLEM_ID = t2.PROBLEM_ID and t3.STATUS = 'AC' limit 1)
else 0 end from (select * from tmp) as t6)
) as tt22 group by tt22.USER_ID) as tt2
on
tt1.USER_ID = tt2.USER_ID
) as tmp2
group by tmp2."USER_ID"
) as tmp3