select
	RANK() OVER(ORDER BY sum_point DESC) as RANK
	,t1.USER_ID as USER_ID
	,sum_point as POINT
	,EX_TIME + ifnull(WRONG_ANS,0) * 300  as EX_TIME
	,ifnull(WRONG_ANS,0) as WRONG_ANS
from 
	(
	select
		USER_ID
		,sum(POINT) as sum_point
	from 
		SUBMISSIONS
	where 
		CONTEST_ID = 2
	group by
		USER_ID
	) t1
left join (
    select
    	t1.USER_ID
    	--,max_sub_at
    	--,t2.STARTED_AT
    	,strftime('%s', max_sub_at) - strftime('%s', t2.STARTED_AT) as EX_TIME
    from 
    	SUBMISSIONS  t1
    left join ENTRIES   t2
    	on t1.CONTEST_ID = t2.CONTEST_ID
    	and t1.USER_ID = t2.USER_ID
    inner join (
        select
        	USER_ID
        	,max(SUBMITTED_AT) as max_sub_at
        from 
        	SUBMISSIONS
        where 
        	CONTEST_ID = 2
        	and STATUS = 'AC'
        group by 
            USER_ID
        ) t3
        on t1.USER_ID = t3.USER_ID
        and t1.SUBMITTED_AT = t3.max_sub_at
    where 
    	t1.CONTEST_ID = 2
    	and t1.STATUS = 'AC'
    order by 
        t1.USER_ID
    ) t2
    on t1.USER_ID = t2.USER_ID
left join (
	select
		USER_ID
		,count(POINT) as WRONG_ANS
	from 
		SUBMISSIONS
	where 
		CONTEST_ID = 2
		and STATUS <> 'AC'
	group by
		USER_ID
    ) t3
    on t1.USER_ID = t3.USER_ID
order by 
	RANK
	,WRONG_ANS
	,t1.USER_ID