20220111061948

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 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 FROM essayReviews AS e JOIN firstReviews AS f USING (userID) WHERE f.cardCount = e.cardCount AND e.cardCount > 80)

SELECT APPROX_QUANTILES(accuracy, 4), COUNT(*) FROM eligibleEssayReviews