20220118104242

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),

essayRanks AS (SELECT cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS essayAccuracy FROM `logs.reviews` WHERE reviewCount = 1 AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND userID IN (SELECT userID FROM eligibleEssayReviews) GROUP BY cardID),
reviewRanks AS (SELECT cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS reviewAccuracy FROM withSessionNumber WHERE sessionNumber = 1 AND userID IN (SELECT userID FROM eligibleEssayReviews) GROUP BY cardID)

SELECT cardID, essayAccuracy, reviewAccuracy, RANK() OVER (ORDER BY essayAccuracy) AS essayRank, RANK() OVER (ORDER BY reviewAccuracy) AS reviewRank FROM essayRAnks JOIN reviewRanks USING (cardID)