WITH TARGET AS 
	(
		SELECT
			S.*
		FROM
			SUBMISSIONS S
			INNER JOIN 
			(SELECT
				USER_ID,
				PROBLEM_ID,
				MAX(SUBMITTED_AT) AS PROB_TIME
			 FROM
				SUBMISSIONS
			 WHERE
			 	ENTRY_ID IS NOT NULL
			 AND STATUS = 'AC'
			 GROUP BY
				USER_ID,
				PROBLEM_ID
			) PROB_LASTTIME
			ON  S.USER_ID = PROB_LASTTIME.USER_ID
			AND S.PROBLEM_ID = PROB_LASTTIME.PROBLEM_ID
		WHERE
			S.SUBMITTED_AT <= PROB_LASTTIME.PROB_TIME
	) ,
	USER_LASTTIME AS 
	(SELECT
		USER_ID,
		MAX(SUBMITTED_AT) AS LAS_TIME
	 FROM
		SUBMISSIONS
	 WHERE
	 	ENTRY_ID IS NOT NULL
	 AND STATUS = 'AC'
	 GROUP BY
	 	USER_ID
	) 
SELECT
	ROW_NUMBER() OVER(ORDER BY POINT DESC) Rank,
	USER_ID,
	POINT,
	EX_TIME,
	WRONG_ANS
FROM
(
SELECT
	E.USER_ID AS USER_ID,
	SUM(T.POINT) AS POINT,
	CAST((JulianDay(L.LAS_TIME) - JulianDay(E.STARTED_AT)) * 24 * 60 * 60 As Integer) + 
	(SUM(CASE WHEN T.STATUS = 'WA' THEN 1 ELSE 0 END) * 300) AS EX_TIME,
	SUM(CASE WHEN T.STATUS = 'WA' THEN 1 ELSE 0 END) AS WRONG_ANS
FROM
	ENTRIES E
	LEFT JOIN TARGET T
	ON E.ENTRY_ID = T.ENTRY_ID
	LEFT JOIN USER_LASTTIME L
	ON E.USER_ID = L.USER_ID
WHERE
	T.POINT IS NOT NULL
GROUP BY
	E.USER_ID 
ORDER BY
	POINT DESC
)
ORDER BY
	RANK,
	WRONG_ANS,
	USER_ID