20210309100759

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=2
    AND isRetry IS NOT TRUE),
    odds AS (
SELECT
  ANY_VALUE(a.cardID) AS aCardID,
  ANY_VALUE(a.question) AS aQuestion,
  a.essayOrder,
  ANY_VALUE(b.cardID) AS bCardID,
  ANY_VALUE(b.question) AS bQuestion,
  b.essayOrder,
  LOG(COUNTIF(b.reviewMarking = "remembered"
      AND b.timestamp > a.timestamp) * COUNTIF(b.reviewMarking="forgotten"
      AND b.timestamp < a.timestamp)/(COUNTIF(b.reviewMarking="remembered"
        AND b.timestamp < a.timestamp) * COUNTIF(b.reviewMarking="forgotten"
        AND b.timestamp > a.timestamp))) AS logOddsRatio
FROM
  samples AS a
JOIN
  samples AS b
USING
  (userID)
WHERE
  a.essayOrder != b.essayOrder
GROUP BY
  a.essayOrder,
  b.essayOrder
)
SELECT aCardID, ANY_VALUE(aQuestion), SUM(logOddsRatio) AS totalLogOdds from odds GROUP BY aCardID ORDER BY totalLogOdds DESC