20220118102516

WITH
users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`
  WHERE
    schedule = "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 AS essayAccuracy, 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)

SELECT ANY_VALUE(essayAccuracy) AS essayAccuracy, COUNTIF(reviewMarking="remembered")/COUNT(*) AS reviewAccuracy, NTILE(100) OVER (ORDER BY ANY_VALUE(essayAccuracy)) AS essayRank, NTILE(100) OVER (ORDER BY COUNTIF(reviewMarking="remembered")/COUNT(*)) AS reviewRank FROM withSessionNumber JOIN eligibleEssayReviews USING (userID) WHERE sessionNumber = 1 GROUP BY userID