WITH
successes AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID ORDER BY timestamp ASC) AS firstTimestamp FROM `logs.reviews` WHERE isRetry IS NOT TRUE AND reviewMarking = "remembered"),
withBestInterval AS (SELECT r.userID AS userID, r.cardID AS cardID, r.timestamp AS timestamp, ANY_VALUE(r.beforeInterval) AS beforeInterval, ANY_VALUE(r.reviewMarking) AS reviewMarking, MAX(s.beforeInterval) AS priorBestInterval, ANY_VALUE(s.firstTimestamp) AS firstTimestamp FROM `logs.reviews` AS r JOIN successes AS s ON (r.cardID = s.cardID AND r.userID = s.userID AND r.timestamp > s.timestamp) WHERE r.isRetry IS NOT TRUE GROUP BY r.userID, r.cardID, r.timestamp),
traces AS (SELECT cardID, userID, reviewInterval, COUNTIF(reviewMarking="forgotten") AS lapseCount, COUNT(*) AS attempts FROM withBestInterval JOIN (SELECT reviewInterval FROM `logs.validIntervals`) ON (withBestInterval.priorBestInterval >= reviewInterval) GROUP BY cardID, userID, reviewInterval HAVING (TIMESTAMP_DIFF(MAX(timestamp), ANY_VALUE(firstTimestamp), DAY) >= 365)),
byUser AS (SELECT reviewInterval, userID, 1 - (SUM(lapseCount)/SUM(attempts)) AS accuracy FROM traces GROUP BY userID, reviewInterval)
SELECT reviewInterval, APPROX_QUANTILES(accuracy, 4) FROM byUser GROUP BY reviewInterval ORDER BY reviewInterval