with POINT_PER_ENTRY as (
    select
    	USER_ID,
    	sum(POINT) as POINT
    from
    	SUBMISSIONS
    where 
    	CONTEST_ID = 2 and 
    	ENTRY_ID is not NULL
    group by 
    	USER_ID
    having sum(POINT) <> 0
),
LAST_AC_TIME_PER_ENTRY as (
    select
    	USER_ID,
    	max(SUBMITTED_AT) as LAST_AC
    from
    	SUBMISSIONS
    where 
    	CONTEST_ID = 2 and 
    	ENTRY_ID is not NULL and 
    	STATUS = 'AC'
    group by 
    	USER_ID
),
WA_COUNT as (
    select
        A.USER_ID,
        count(0) as WA_CNT
    from
    	SUBMISSIONS A
    inner join
        LAST_AC_TIME_PER_ENTRY B
    on
        A.CONTEST_ID = 2 and
        A.USER_ID = B.USER_ID and
    	A.STATUS = 'WA' and 
    	A.SUBMITTED_AT <= B.LAST_AC
    group by 
    	A.USER_ID
)
select
*
from
(
select
    RANK() OVER(ORDER BY B.LAST_AC + C.WA_CNT * 300 DESC, B.LAST_AC asc) AS 'RANK',
    D.USER_ID as USER_ID,
    (B.LAST_AC + C.WA_CNT * 300) as POINT,
    B.LAST_AC as EX_TIME,
    C.WA_CNT as WRONG_ANS
from
    POINT_PER_ENTRY A
inner join
    LAST_AC_TIME_PER_ENTRY B
on
    A.USER_ID = B.USER_ID
inner join
    WA_COUNT C
on
    A.USER_ID = C.USER_ID
inner join
    ENTRIES D
on
    A.USER_ID = D.USER_ID
) DATA
order by
    RANK asc,
    WRONG_ANS asc,
    USER_ID asc