20210309102137

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
    reviewNumber=1
    AND isRetry IS NOT TRUE),
    
userSamples AS (SELECT userID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS N FROM firstSessionSamples GROUP BY userID HAVING N = 112)

SELECT bucket/100 AS bucket, IFNULL(N, 0) AS N FROM (SELECT FLOOR(accuracy*100) AS bucket, COUNT(*) AS N from userSamples GROUP BY bucket) RIGHT JOIN (SELECT bucket AS bucket FROM UNNEST(GENERATE_ARRAY(0, 100, 1)) AS bucket) USING (bucket) ORDER BY bucket ASC