-- 点数合計
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_ans_time as (SELECT strftime('%s', sub.submitted_at) - strftime('%s', ent.started_at) as ans_time,
sub.USER_ID as usr,
PROBLEM_ID as prb
FROM SUBMISSIONS as sub
JOIN (SELECT * FROM ENTRIES WHERE CONTEST_ID = '2') as ent
ON sub.USER_ID = ent.USER_ID
WHERE sub.entry_id IS NOT NULL
AND sub.CONTEST_ID = '2'),
-- 合計解答時間
total_ans_time as (
SELECT sum(ans_time) as ans_time, usr as user_id
FROM ac_ans_time
GROUP BY 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 + wa_num*300 ASC) as RANK,
users.user_id as USER_ID,
total_point as POINT,
ans_time + 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
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;