WITH cut AS(
	SELECT
		USER_ID
		,SUM(POINT) AS total_pt
	FROM
		SUBMISSIONS
	WHERE
		CONTEST_ID = 2
	GROUP BY
		USER_ID
	HAVING
		total_pt > 0
)

, target AS (
	SELECT
		s.USER_ID
		,e.STARTED_AT
		,s.PROBLEM_ID
		,s.SUBMITTED_AT
		,s.STATUS
		,s.POINT
	FROM
		SUBMISSIONS as s
	INNER JOIN
		ENTRIES as e
	ON
		s.ENTRY_ID = e.ENTRY_ID
	INNER JOIN
		cut
	ON
		s.USER_ID = cut.USER_ID
	WHERE
		s.CONTEST_ID = 2
	AND 
		s.ENTRY_ID IS NOT NULL
)

, st as (
	SELECT
		USER_ID
		,MAX(CASE WHEN STATUS = 'AC' THEN STARTED_AT ELSE NULL END) as max_ac_time
	FROM
		target
	GROUP BY
		USER_ID
)

, ag as (
	SELECT
		USER_ID
		, SUM(POINT) as POINTS
		, SUM(CASE WHEN STATUS = 'WA' THEN 1 ELSE 0 END) as wa_cnt
	FROM
		target
	GROUP BY
		USER_ID
)

, agg as (
	SELECT
		USER_ID
		,POINTS
		,wa_cnt
		,times + (wa_cnt * 300) as total_tm
	FROM
		(
			SELECT
				a.USER_ID
				,MAX(POINTS) AS POINTS
				,MAX(wa_cnt) as wa_cnt
				,SUM(strftime('%s', max_ac_time) - strftime('%s', STARTED_AT)) as times
				-- ,times + (wa_cnt * 300) as total_tm
			FROM
				(
					SELECT
						USER_ID
						,STARTED_AT
					FROM
						target
				) as a
			JOIN
				ag
			ON
				a.USER_ID = ag.USER_ID
			JOIN
				st
			ON
				a.USER_ID = st.USER_ID
			GROUP BY
				a.USER_ID
		)
)


SELECT
	RANK() OVER(ORDER BY POINTS DESC, total_tm ASC) AS RANK
	,USER_ID
	,POINTS as  POINT
	,total_tm as EX_TIME
	,wa_cnt as WRONG_ANS
FROM
	agg
ORDER BY
	RANK
	,WRONG_ANS
	,USER_ID