WITH
users AS (SELECT * FROM `logs.registeredUsers` WHERE schedule != "original" AND schedule != "aggressiveStart"),
initiallyForgotten AS (SELECT DISTINCT userID, cardID FROM (SELECT * EXCEPT (reviewCount), RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewCount FROM `logs.reviews`) JOIN `users` USING (userID) WHERE reviewCount = 1 AND cardID IN UNNEST(["UN7bsEiQmjXFQ9wsPjvL",
"v3vTMn9bAA0joehFnsRK",
"LorT6689duDMfrlASd3O",
"ZYogO9HQAS9M4DkFSWh7"])),
subsequentReviews AS (SELECT *, 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 AND isRetry IS NOT TRUE AND beforeInterval > 1000*60*60*24)
SELECT beforeInterval / (1000*60*60*24), COUNT(DISTINCT userID) AS N, COUNT(*) AS promptN, COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy FROM subsequentReviews WHERE fullReviewCount = 1 GROUP BY beforeInterval ORDER BY beforeInterval