WITH
users AS (SELECT * FROM `logs.registeredUsers` WHERE schedule NOT IN UNNEST(["original", "aggressiveStart"])),
initiallyForgotten AS (SELECT DISTINCT userID, cardID, reviewMarking FROM `logs.reviews` JOIN `users` USING (userID) WHERE reviewCount = 1 AND cardID IN (
SELECT
cardID
FROM
`logs.latestEssaysCards`
WHERE
essayName="qcvc")),
subsequentReviews AS (SELECT * EXCEPT (reviewMarking), initiallyForgotten.reviewMarking AS reviewMarking, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS fullReviewCount FROM `logs.reviews` AS r JOIN initiallyForgotten USING (userID, cardID) WHERE sessionID IS NOT NULL),
samples AS (SELECT MAX(beforeInterval) / (1000*60*60*24) AS days, COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy FROM subsequentReviews WHERE fullReviewCount = 1 GROUP BY userID HAVING COUNT(*) >= 103)
SELECT days, APPROX_QUANTILES(accuracy, 4), COUNT(*) FROM samples GROUP BY days ORDER BY days