WITH
users AS (SELECT * FROM `logs.registeredUsers` WHERE schedule NOT IN UNNEST(["original", "aggressiveStart"])),
initiallyForgotten AS (SELECT DISTINCT userID, cardID FROM `logs.reviews` JOIN `users` USING (userID) WHERE reviewCount = 1 AND cardID IN UNNEST(["THZdeQbFFriDCeLG8NVS",
"buvxh7iNAE0OGCWKLmlA",
"S0ijLyYsCv8orVGWDhAd",
"sCR6RomX0y6hO1TfclMI",
"ONHWQIqjO2apC3D5Wq00",
"r0aPQB8qnSUWV0VFZVnP",
"uUZf1VOOSdQxrjhtOQt1",
"xS1sny5dpoCj5zYIUdxM",
"xU1NlPqrYsiCpUbh2G26",
"wsfXIAGNrOJLlbaDgpiS",
"adumnXmObq4O5kGgywRv",
"bS3XyvMqqsailbuPMnDR",
"NTt5NM7gqPotdxg3vUs0",
"0LBqNCdJS6UIu5qEbPjI",
"ZYogO9HQAS9M4DkFSWh7",
"saK28MyBHCsT9nc7N49u",
"LFA9ZWkL4cnkt1ijMjeg",
"dyYV5qf5yBByn0j6SqWX",
"KauR2SOrJtvoEBJTaVK0",
"Az4W8dbzBXmiO4ZAQykL",
"v3vTMn9bAA0joehFnsRK",
"Mh1cQe6i5bxHnkPsakEs",
"wFliehYIPjRrDY2mdvOr",
"z7cOpsVhlVn3HJWB2VET",
"4UJEzb4bQOqDlHcHzqRV",
"Dmvs7ezW9r77URieTjEh",
"6ovcP28jH0upWr7kP4I9",
"DsKjxLEnyp7KzC93t1YX",
"17fsggSIuqxnhKFwOI2g",
"LorT6689duDMfrlASd3O",
"UN7bsEiQmjXFQ9wsPjvL",
"OnKhM42b4nU5JAf76PxU",
"h1AXHXVtsGKxkamS8Hb2",
"6NRxe1wnphNsMjTZ9LXf",
"1ENYKL02RT8aidhI917F",
"xiNW1zgeb2ITHGi6uQtg",
"s8duZcGBbu0dxb4xEAGg",
"mI5Sf36iMrDKMpdFHdWU",
"CvWy7vLttDaNz3fhpGhe",
"BCZEC16X1jeMuLGlrs92",
"44wRCJhM0SSLoyfFyhDs",
"Sgl5VJI79NB6b22EqTn3",
"VvKSgUzXwzfNRJFsYyjE",
"VpMt0CzSgOqVrdtTReHa",
"mRJYkVyrFikg9PzfWUgd",
"bcbyBXRiH8lYkzIPuA6e",
"HpQN6NxQs6Y8kdS6XOVY",
"6H3CMpGxVyuLBaJKZGhI"])),
subsequentReviews AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS fullReviewCount FROM `logs.reviews` JOIN initiallyForgotten USING (userID, cardID) WHERE sessionID IS NOT NULL AND beforeInterval > 1000*60*60*48)
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