20211130174838

WITH
  users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`
  WHERE
    schedule NOT IN UNNEST(["original", "aggressiveStart"])),
initialReviews AS (
    SELECT userID,
    ANY_VALUE(schedule) AS schedule,
    COUNT(DISTINCT cardID) AS initialCount FROM `logs.reviews` JOIN users USING (userID) WHERE sessionID IS NULL AND cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc") GROUP BY userID
),
  withRank AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS repetitionNumber
  FROM
    `logs.reviews`
  WHERE
    sessionID IS NOT NULL AND isRetry IS FALSE AND beforeInterval > 1000*60*60*24
    AND cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc"))

-- SELECT * FROM withRank  JOIN
--     initialReviews
--   USING
--     (userID)
--  WHERE repetitionNumber = 1 AND beforeInterval/(1000*60*60*24)=60 ORDER BY userID, cardID

  SELECT
    beforeInterval/(1000*60*60*24) AS days,
    COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy,
    COUNT(DISTINCT userID) AS userCount,
    COUNT(*) AS reviewCount
  FROM
    withRank
  JOIN
    initialReviews
  USING
    (userID)
  WHERE
    repetitionNumber = 1
  GROUP BY days ORDER BY days