-- 点数合計
WITH total_point as (SELECT SUM(POINT) as total_point, USER_ID FROM SUBMISSIONS as sub
WHERE entry_id IS NOT NULL
AND CONTEST_ID = '2'
AND POINT != 0
GROUP BY USER_ID),
-- 最終AC解答時間
last_ac_ans_time as (
SELECT max(sub.submitted_at) as submitted_at,
sub.USER_ID
FROM SUBMISSIONS as sub
WHERE sub.entry_id IS NOT NULL
AND sub.CONTEST_ID = '2'
AND status = 'AC'
GROUP BY USER_ID),
-- 解答時間
total_ans_time as (
SELECT strftime('%s', sub.submitted_at) - strftime('%s', ent.started_at) as ans_time,
sub.USER_ID
FROM last_ac_ans_time as sub
JOIN (SELECT * FROM ENTRIES WHERE CONTEST_ID = '2') as ent
ON sub.USER_ID = ent.USER_ID),
-- 正当
ac as(
SELECT user_id, submitted_at, problem_id
FROM SUBMISSIONS
WHERE entry_id IS NOT NULL
AND STATUS = 'AC'
AND CONTEST_ID = '2'
),
-- 誤答数計
wa_num as (SELECT COUNT(*) as wa_num, sub.USER_ID as user_id FROM SUBMISSIONS as sub
JOIN ac
ON sub.problem_id = ac.problem_id
AND sub.user_id = ac.user_id
WHERE entry_id IS NOT NULL
AND sub.submitted_at < ac.submitted_at
AND STATUS != 'AC'
AND CONTEST_ID = '2'
GROUP BY sub.USER_ID),
-- ユーザ一覧
users as (SELECT DISTINCT user_id FROM SUBMISSIONS)
SELECT
RANK() OVER(ORDER BY total_point DESC, ans_time + CASE WHEN wa_num IS NULL THEN 0 ELSE wa_num END*300 ASC) as RANK,
users.user_id as USER_ID,
total_point as POINT,
CASE WHEN ans_time IS NULL THEN 0 ELSE ans_time END + CASE WHEN wa_num IS NULL THEN 0 ELSE wa_num END*300 as EX_TIME,
CASE WHEN wa_num IS NULL THEN 0 ELSE wa_num END as WRONG_ANS
FROM users
JOIN total_point ON users.user_id = total_point.user_id
LEFT JOIN total_ans_time ON users.user_id = total_ans_time.user_id
LEFT JOIN wa_num ON users.user_id = wa_num.user_id;