20220203085524

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*20 AND isRetry IS FALSE AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc")),

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

eligibleEssayReviews AS (SELECT userID, e.accuracy AS essayAccuracy FROM essayReviews AS e JOIN reviews AS f USING (userID)/* WHERE f.cardCount >= e.cardCount AND e.cardCount > 80*/),

quartileUsers AS (SELECT * 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 AND essayAccuracy <= 0.83 AND (cardID="s8duZcGBbu0dxb4xEAGg" OR cardID="xiNW1zgeb2ITHGi6uQtg") GROUP BY beforeInterval, cardID ORDER BY beforeInterval, accuracy