20220126164531

WITH
users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`
  WHERE
    schedule NOT IN UNNEST(["original", "aggressiveStart"])),
    
essayReviews AS (SELECT userID, COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy, COUNT(*) AS cardCount FROM `logs.reviews` JOIN users USING (userID) WHERE reviewCount = 1 AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") GROUP BY userID),

withSessionNumber AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS sessionNumber FROM `logs.reviews` WHERE sessionID IS NOT NULL AND beforeInterval > 1000*60*60*24*2 AND isRetry IS FALSE AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc")),

firstReviews AS (SELECT userID, COUNT(*) AS cardCount FROM withSessionNumber JOIN users USING (userID) WHERE sessionNumber = 1 GROUP BY userID),

eligibleEssayReviews AS (SELECT userID, e.accuracy, NTILE(4) OVER(ORDER BY accuracy) AS quartile FROM essayReviews AS e JOIN firstReviews AS f USING (userID) WHERE f.cardCount >= e.cardCount * 0.95 AND e.cardCount > 50),

quartileUsers AS (SELECT userID FROM eligibleEssayReviews)

SELECT beforeInterval / (1000*60*60*24), COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS reviewCount FROM withSessionNumber JOIN quartileUsers USING (userID) WHERE sessionNumber = 1 GROUP BY beforeInterval, userID ORDER BY beforeInterval, accuracy