20210126102352

WITH
  logs AS (
  SELECT
    *,
    COUNTIF(reviewMarking="forgotten"
      AND sessionID IS NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS essayLapses,
    COUNTIF(reviewMarking="forgotten"
      AND sessionID IS NOT NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS sessionLapses,
    COUNTIF(isRetry IS TRUE
      AND sessionID IS NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS essayRetries,
    COUNTIF(isRetry IS TRUE
      AND sessionID IS NOT NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS sessionRetries,
    MIN(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS firstTimestamp,
    COUNT(DISTINCT sessionID) OVER (PARTITION BY userID, cardID) AS sessionNumber,
    RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber,
    TIMESTAMP_DIFF(LAG(nextDueTimestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), MILLISECOND) AS delta
  FROM
    `logs.reviews`
  WHERE
    cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc") ),
  samples AS (
  SELECT
    *,
  IF
    (essayLapses >= 1
      AND essayRetries >= 1
      AND reviewNumber = essayRetries + 2
      AND sessionID IS NOT NULL
      AND isRetry IS NOT TRUE, -- i.e. they retried successfully within the essay
      "withRetry",
    IF
      (essayLapses = 1
        AND reviewNumber = 2
        AND isRetry IS FALSE
        AND sessionID IS NOT NULL,
        "skipRetry",
        IF (essayLapses = 0 AND sessionLapses = 0 AND reviewNumber = 2 AND isRetry IS FALSE AND sessionID IS NOT NULL, "firstCorrect", NULL))) AS branch
  FROM
    logs
  WHERE
    firstTimestamp >= TIMESTAMP("2020-12-12") )


SELECT branch, COUNT(*) AS N, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy FROM samples GROUP BY branch ORDER BY branch