20210322102651

WITH

attemptLogs AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID) AS firstTimestamp, MAX(timestamp) OVER (PARTITION BY cardID, userID) AS lastTimestamp, RANK() OVER (PARTITION BY cardID, userID ORDER BY timestamp) AS reviewNumber FROM `logs.reviews` WHERE isRetry IS NOT TRUE),

withLapsesInPeriod AS (SELECT *, COUNTIF(reviewMarking="forgotten" AND TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) < 180) OVER (PARTITION BY userID, cardID) AS lapseCount FROM attemptLogs WHERE TIMESTAMP_DIFF(lastTimestamp, firstTimestamp, DAY) >= 180)

SELECT APPROX_QUANTILES(lapseCount, 100) FROM withLapsesInPeriod WHERE reviewNumber = 1