with
SUM_POINT_01 AS
(
select
    a.USER_ID
,   a.CONTEST_ID
,   a.ENTRY_ID
,   a.STARTED_AT
,   max(b.SUBMITTED_AT) as SUBMITTED_AT
,   sum(b.POINT) as POINT
from
    ENTRIES a
inner join
    SUBMISSIONS b
on
    a.CONTEST_ID = b.CONTEST_ID
and a.ENTRY_ID = b.ENTRY_ID
where
    a.CONTEST_ID = 2
and b.POINT <> 0
--and a.USER_ID = 64     -- TEST
group by
    a.USER_ID
,   a.STARTED_AT
),
SUM_POINT_02 AS
(
SELECT
    A.*
,   strftime('%s', A.SUBMITTED_AT) - strftime('%s', A.STARTED_AT) AS EX_TIME_WK
FROM
    SUM_POINT_01 A
),

WRONG_ANS_01 AS
(
select
    a.USER_ID
,   a.CONTEST_ID
,   a.ENTRY_ID
,   B.PROBLEM_ID
,   a.STARTED_AT
,   max(b.SUBMITTED_AT) as SUBMITTED_AT
from
    ENTRIES a
inner join
    SUBMISSIONS b
on
    a.CONTEST_ID = b.CONTEST_ID
and a.ENTRY_ID = b.ENTRY_ID
where
    a.CONTEST_ID = 2
and b.POINT <> 0
--and a.USER_ID = 64     -- TEST
group by
    a.USER_ID
,   a.CONTEST_ID
,   a.ENTRY_ID
,   B.PROBLEM_ID
,   a.STARTED_AT
),
WRONG_ANS_02 AS
(
SELECT
    A.USER_ID
,   A.CONTEST_ID
,   A.ENTRY_ID
,   COUNT(B.SUBMITTED_AT) AS WRONG_ANS
FROM
    WRONG_ANS_01 A
INNER JOIN
    SUBMISSIONS B
ON
    A.CONTEST_ID = B.CONTEST_ID
AND A.ENTRY_ID = B.ENTRY_ID
AND A.PROBLEM_ID = B.PROBLEM_ID
WHERE
    A.CONTEST_ID = 2
AND B.STATUS <> 'AC'
AND B.SUBMITTED_AT < A.SUBMITTED_AT
GROUP BY
    A.USER_ID
,   A.CONTEST_ID
,   A.ENTRY_ID
),
SUM_POINT_03 AS
(
SELECT
    A.*
,   CASE
      WHEN B.CONTEST_ID IS NOT NULL THEN B.WRONG_ANS
      ELSE 0
    END AS WRONG_ANS
,   A.EX_TIME_WK +
    CASE
      WHEN B.CONTEST_ID IS NOT NULL THEN B.WRONG_ANS
      ELSE 0
    END *
    300 AS EX_TIME
FROM
    SUM_POINT_02 A
LEFT OUTER JOIN
    WRONG_ANS_02 B
ON
    A.CONTEST_ID = B.CONTEST_ID
AND A.ENTRY_ID = B.ENTRY_ID
),
SUM_POINT_04 AS
(
SELECT
    A.*
,   RANK() OVER(ORDER BY A.POINT DESC,A.EX_TIME) AS RANK
FROM
    SUM_POINT_03 A
)
SELECT
    A.RANK
,   A.USER_ID
,   A.POINT
,   A.EX_TIME
,   A.WRONG_ANS
FROM
    SUM_POINT_04 A
ORDER BY
    A.RANK
,   A.WRONG_ANS
,   A.USER_ID