20210708115913

WITH
  users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`),
  withRank AS (
  SELECT
    *,
    COUNTIF(sessionID IS NOT NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS repetitionNumber
  FROM
    `logs.reviews`
  WHERE
    cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc")),
  withAccuracies AS (
  SELECT
    userID,
    ANY_VALUE(schedule) AS schedule,
    COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy
  FROM
    withRank
  JOIN
    users
  USING
    (userID)
  WHERE
    repetitionNumber = 1
  GROUP BY
    userID
  HAVING
    COUNT(*) >= 103)
SELECT
  schedule,
  (
  SELECT
    COUNT(*)
  FROM
    `logs.registeredUsers` AS r
  WHERE
    r.schedule = a.schedule
  GROUP BY
    r.schedule) AS totalRegistered,

    (SELECT COUNT(*) FROM (SELECT userID, ANY_VALUE(schedule) AS schedule FROM withRank JOIN users USING (userID) WHERE reviewCount = 1 GROUP BY userID HAVING COUNT(*) >= 103) AS r WHERE r.schedule = a.schedule GROUP BY schedule) AS totalCollected,
  COUNT(*) AS N,
  APPROX_QUANTILES(accuracy, 10) AS quantiles
FROM
  withAccuracies AS a
GROUP BY
  schedule
ORDER BY
  schedule