select
RANK() OVER (ORDER BY a.sp desc, (b.time + IFNULL(c.wa, 0)*300)) RANK
,a.user_id USER_ID
, a.sp POINT
,(b.time + IFNULL(c.wa, 0)*300) EX_TIME
, IFNULL(c.wa, 0) WRONG_ANS
from
(-- 合計点数
select
s.USER_ID
, sum(s.POINT) sp
from SUBMISSIONS s
where
    s.CONTEST_ID = 2
    and s.ENTRY_ID is not null
group by s.USER_ID
having sp != 0)a
join
(select s2.USER_ID, max(strftime('%s', s2.SUBMITTED_AT) - strftime('%s', e2.STARTED_AT)) time from SUBMISSIONS s2 join ENTRIES e2 on s2.ENTRY_ID = e2.ENTRY_ID where s2.CONTEST_ID = 2
    and s2.ENTRY_ID is not null and s2.STATUS = 'AC' group by s2.user_id)b on a.user_id = b.user_id
left outer join(
-- 問題別誤答数
select 
s3.USER_ID, count(*) wa
from SUBMISSIONS s3 
where s3.SUBMITTED_AT < (select max(s4.SUBMITTED_AT) from SUBMISSIONS s4 where s4.status = 'AC' and s4.CONTEST_ID = 2 and s4.ENTRY_ID is not null and s3.USER_ID = s4.USER_ID and s3.PROBLEM_ID = s4.PROBLEM_ID ) 
group by s3.user_id
)c on b.user_id = c.user_id