WITH PENA AS(
SELECT S.ENTRY_ID,
COUNT SUBMIT_ID
FROM SUBMISSIONS as S
INNER JOIN (
SELECT ENTRY_ID,
PROBLEM_ID,
MAX(SUBMITTED_AT) as LAST
FROM SUBMISSIONS
WHERE STATUS='AC'
and ENTRY_ID is not null
and CONTEST_ID=2
GROUP BY ENTRY_ID,PROBLEM_ID
)as SUB
ON S.ENTRY_ID=SUB.ENTRY_ID
and S.PROBLEM_ID=SUB.PROBLEM_ID
and S.SUBMITTED_AT<SUB.LAST
GROUP BY S.ENTRY_ID),
SCORE_TIME as (
SELECT ENTRY_ID,
SUM(POINT) as POI,
MAX(SUBMITTED_AT) as LAST
FROM SUBMISSIONS as S
WHERE STATUS='AC'
and ENTRY_ID is not null
and CONTEST_ID=2
GROUP BY ENTRY_ID)