20220202122723

WITH
  users AS (
  SELECT
    * EXCEPT (schedule), SUBSTR(schedule, 0, 9) AS schedule
  FROM
    `logs.registeredUsers` WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 45 DAY) AND timestamp >= TIMESTAMP("2021-04-19")),
  withRank AS (
  SELECT
    *,
    COUNTIF(sessionID IS NOT NULL AND isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS repetitionNumber,
    COUNTIF(reviewMarking = "remembered" AND isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS correctCount,
  FROM
    `logs.reviews`
  WHERE
    cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc")),
  userCardCounts AS (SELECT userID, ANY_VALUE(schedule) AS schedule, COUNTIF(reviewMarking="remembered")/COUNT(*) AS essayAccuracy, COUNT(DISTINCT cardID) AS essayCardCount FROM users JOIN (SELECT * FROM withRank WHERE reviewCount = 1) USING (userID) GROUP BY userID),
  validUsers AS (SELECT userID, ANY_VALUE(schedule) AS schedule, ANY_VALUE(essayAccuracy) AS essayAccuracy FROM userCardCounts JOIN (SELECT * FROM withRank WHERE correctCount=1 AND sessionID IS NOT NULL AND isRetry IS NOT TRUE) USING (userID) GROUP BY userID HAVING COUNT(*) >= 0.95*ANY_VALUE(essayCardCount) AND ANY_VALUE(essayCardCount) >= 50 AND COUNTIF(cardID="s8duZcGBbu0dxb4xEAGg" OR cardID="xiNW1zgeb2ITHGi6uQtg") >= 1)
  

--   SELECT schedule, cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS N FROM withRank JOIN validUsers USING (userID) WHERE correctCount = 1 AND sessionID IS NOT NULL AND isRetry IS NOT TRUE AND (cardID="s8duZcGBbu0dxb4xEAGg" OR cardID="xiNW1zgeb2ITHGi6uQtg") GROUP BY schedule, cardID ORDER BY schedule, cardID
  SELECT schedule, cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, APPROX_QUANTILES(essayAccuracy, 4) AS essayAccuracyQuantiles, COUNT(*) AS N FROM withRank JOIN validUsers USING (userID) WHERE correctCount = 1 AND sessionID IS NOT NULL AND isRetry IS NOT TRUE AND (cardID="s8duZcGBbu0dxb4xEAGg" OR cardID="xiNW1zgeb2ITHGi6uQtg") GROUP BY schedule, cardID ORDER BY schedule, cardID