20211116152859

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([“HpQN6NxQs6Y8kdS6XOVY”,
“1ENYKL02RT8aidhI917F”,
“DsKjxLEnyp7KzC93t1YX”,
“6H3CMpGxVyuLBaJKZGhI”,
“xiNW1zgeb2ITHGi6uQtg”,
“s4N3L6iTy93DK5F0mh6i”,
“s8duZcGBbu0dxb4xEAGg”,
“oEceJqT6i3VlX5R0vyjT”,
“Sgl5VJI79NB6b22EqTn3”,
“z7cOpsVhlVn3HJWB2VET”])),

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