20220120173038

WITH
users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`
  WHERE
    schedule NOT IN UNNEST(["original", "aggressiveStart"])),
    
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")),

validUsers AS (SELECT userID FROM withSessionNumber JOIN users USING (userID) WHERE sessionNumber = 1 GROUP BY userID HAVING COUNT(DISTINCT cardID) >= 100 AND COUNT(DISTINCT beforeInterval)=1),

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

SELECT days, COUNTIF(accuracy >= 0.9)/COUNT(*), COUNTIF(accuracy >= 0.75)/COUNT(*), COUNTIF(accuracy>=0.5)/COUNT(*), SUM(userCount) FROM samples GROUP BY days ORDER BY days