20210408112811

WITH
eligibleUsers AS (SELECT userID FROM `logs.registeredUsers` WHERE schedule="aggressiveStart"),
logs AS (SELECT *, COUNTIF(isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber, COUNTIF(reviewMarking="forgotten") OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lapseCount, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), HOUR) AS reviewInterval FROM `logs.reviews` JOIN eligibleUsers USING (userID))

SELECT ROUND(reviewInterval / 24) AS days, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS N FROM logs WHERE reviewNumber=2 AND isRetry IS NOT TRUE AND lapseCount >= 1 GROUP BY days ORDER BY days ASC