WITH conditions AS (SELECT userID, JSON_EXTRACT_SCALAR(extra, "$['condition']") AS condition FROM `metabook-qcc.logs.userEvents` WHERE timestamp >= TIMESTAMP("2020-01-28") AND JSON_EXTRACT_SCALAR(extra, "$['experimentName']") = "efficacy2" ORDER BY condition),
enrolledUsers AS (SELECT userID, condition, timestamp as enrollmentTimestamp FROM (SELECT * FROM `metabook-qcc.logs.userEvents` WHERE eventName="efficacy2Enroll") JOIN conditions USING (userID)),
cards AS (SELECT * FROM UNNEST(([ "h1AXHXVtsGKxkamS8Hb2",
"6NRxe1wnphNsMjTZ9LXf",
"xQte6vwqUwy2j5OWdxjK",
"1ENYKL02RT8aidhI917F",
"zRgw2osbesLEQ5ADo8DD",
"E4g8wyr2folQ87Slf3ZO",
"7QM6pEJ9bxZHK50e1kww",
"xiNW1zgeb2ITHGi6uQtg",
"s8duZcGBbu0dxb4xEAGg"
])) AS cardID),
relevantReviews AS (SELECT userID, cardID, reviewMarking, timestamp, sessionID, reviewCount FROM `metabook-qcc.logs.reviews` WHERE timestamp >= TIMESTAMP("2020-01-28") AND userID IN (SELECT userID FROM enrolledUsers) AND cardID IN (SELECT cardID FROM cards)),
samples AS (SELECT userID, cardID, reviewMarking FROM (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp ASC) as reviewNumber FROM relevantReviews WHERE sessionID IS NOT NULL AND timestamp >= TIMESTAMP_ADD((SELECT enrollmentTimestamp FROM enrolledUsers WHERE enrolledUsers.userID = relevantReviews.userID), INTERVAL 30 DAY)) WHERE reviewNumber = 1),
initialAccuracies AS (SELECT userID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS essayAccuracy FROM `logs.reviews` WHERE reviewCount=1 AND sessionID IS NULL AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND userID IN (SELECT userID FROM enrolledUsers) GROUP BY userID),
results AS (SELECT condition, cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS userFraction, COUNT(*) AS N, ANY_VALUE(question) AS question FROM samples JOIN enrolledUsers USING (userID) JOIN `logs.latestEssaysCards` USING (cardID) JOIN initialAccuracies USING (userID) WHERE essayAccuracy <= 0.83 GROUP BY condition, cardID HAVING condition != "delay5Days")
SELECT cardID, reviewFraction, noReviewFraction, N, reviewFraction - noReviewFraction, question FROM (SELECT userFraction AS reviewFraction, cardID, question FROM results WHERE condition="control") JOIN (SELECT userFraction AS noReviewFraction, N, cardID FROM results WHERE condition="delay1Month") USING (cardID) ORDER BY noReviewFraction ASC