WITH
attempts AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID ORDER BY timestamp ASC) AS firstTimestamp, MAX(timestamp) OVER (PARTITION BY userID, cardID) AS maxTimestamp FROM `logs.reviews` WHERE isRetry IS NOT TRUE),
traces AS (SELECT cardID, userID, COUNTIF(reviewMarking="forgotten") AS lapseCount FROM attempts WHERE TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) < 180 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