20210309104455

WITH
  cards AS (
  SELECT
    cardID,
    question,
    answer,
    essayOrder
  FROM
    `logs.latestEssaysCards`
  WHERE
    essayName="qcvc"),
  samples AS (
  SELECT
    *,
    COUNTIF(isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber
  FROM
    `logs.reviews`
  JOIN
    cards
  USING
    (cardID)),
  firstSessionSamples AS (
  SELECT
    *
  FROM
    samples
  WHERE
    isRetry IS NOT TRUE),
    
userSamples AS (SELECT userID, reviewNumber, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS N FROM firstSessionSamples GROUP BY userID, reviewNumber HAVING N = 112)

SELECT reviewNumber, AVG(accuracy) AS mean, STDDEV(accuracy) AS stddev, COUNT(*) AS N FROM userSamples GROUP BY reviewNumber ORDER BY reviewNumber