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, MAX(r.timestamp) OVER (PARTITION BY r.cardID, r.userID) AS maxTimestamp, 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, COUNTIF(reviewMarking="forgotten") AS lapseCount FROM withBestInterval WHERE TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) < 180 AND withBestInterval.priorBestInterval >= 432000000 GROUP BY cardID, userID HAVING (TIMESTAMP_DIFF(ANY_VALUE(maxTimestamp), ANY_VALUE(firstTimestamp), DAY) >= 180)),
perUser AS (SELECT userID, COUNT(DISTINCT cardID) AS cardCount, SUM(lapseCount) AS totalLapses FROM traces GROUP BY userID)
SELECT APPROX_QUANTILES(totalLapses/cardCount, 10) FROM perUser WHERE cardCount > 50