20220112085959

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 AND e.cardCount > 80*/),

quartileUsers AS (SELECT userID FROM eligibleEssayReviews)

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