WITH
eligibleUsers AS (SELECT userID FROM `logs.reviews` JOIN `logs.latestEssaysCards` USING (cardID) WHERE essayName="qcvc" GROUP BY userID HAVING COUNT(DISTINCT cardID) >= 103),
reviews AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber FROM `logs.reviews` JOIN eligibleUsers USING (userID) JOIN (SELECT essayName, cardID FROM `logs.latestEssaysCards`) USING (cardID) WHERE essayName="qcvc"),
byUser AS (SELECT userID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, TIMESTAMP_DIFF(MAX(timestamp), MIN(timestamp), MINUTE) AS readingTime, COUNT(*) AS N FROM reviews WHERE reviewNumber = 1 GROUP BY userID),
enteredUserEmails AS (SELECT email, MIN(entryNumber) AS entryNumber FROM UNNEST([
"ne6c.skoberne@gmail.com",
"aklounts@gmail.com",
"tajobin@protonmail.com",
"Freethinkingaway@gmail.com",
"abhishekvempati@yahoo.com",
"favrot@gmail.com",
"mbielik24@hotmail.com",
"abhishekvempati@yahoo.com",
"sgondala2@gmail.com",
"dukeatalay@me.com",
"mark.hulme@gmail.com",
"guzt44@gmail.com",
"paulrorgan@gmail.com",
"tenaciousd0313@hotmail.com",
"boston.gubler@gmail.com",
"hubbersnaks@gmail.com",
"harry.orchard95@gmail.com",
"ryanhj@gmail.com",
"steven@thestrong.life",
"Jonahhaefner@gmail.com",
"sosariel98@gmail.com",
"marc.rudajev@gmail.com",
"marc.rudajev@gmail.com",
"pete.lebedev@gmail.com",
"j.raaizn@gmail.com",
"j.raizn@gmail.com",
"imaxblue88@gmail.com",
"ian96turner@gmail.com",
"jamesogbeide@gmail.com",
"Pablomorgui99@gmail.com ",
"paul.royere@gmail.com",
"m.a.mcguin@gmail.com",
"Benrburckel@gmail.com",
"priyaravi.22@gmail.com",
"zac@kelt.com.au",
"suppal3@wisc.edu",
"Ned@status.im",
"mikaivonengel@gmail.com",
"ocorrea7@gmail.com",
"Kdlynch12@gmail.com",
"ck@vacantsky.com",
"nagaranudit@gmail.com",
"philipp.kiesele@hotmail.com",
"Ckuo23@yahoo.com",
"powellaf@gmail.com",
"g4u007@gmail.com",
"jaakko.jarviniemi@gmail.com",
"itsjackwalton@gmail.com",
"Surfer2@protonmail.com",
"Frostyandy2k@yahoo.com",
"mohoyt@gmail.com",
"johannes.jolkkonen@gmail.com",
"warrenbhw@gmail.com",
"calvin.winchester@gmail.com",
"nikhil2182@gmail.com",
"nikhil2182@gmail.com",
"dantenel88+quantumcountry@gmail.com",
"Ohmr27@gmail.com",
"thehealthyanalyst@gmail.com",
"mertserezli@gmail.com",
"ankitsingh26548@gmail.com",
"merrilpious+1729@gmail.com",
"Mx02.mail@icloud.com",
"jetmirh@gmx.de",
"mcmac.lm@gmail.com",
"nikhilreddydev@gmail.com",
"Juan.nadal@gmail.com",
"pauloesteves8@gmail.com",
"camelliayang1220@gmail.com",
"ishwant2001@gmail.com",
"gaszczyk.grzegorz@gmail.com",
"jakob.proos@gmail.com",
"jasreen1098@gmail.com",
"jatinsood1024@gmail.com",
"andrey.a.semenov@gmail.com",
"edwardtay7@gmail.com",
"chaecramb@gmail.com",
"progger@gmail.com",
"jetmirh@gmx.de",
"parth29@gmail.com",
"sylksie@pm.me",
"trisportfan@gmail.com",
"paul@pmolloy.com",
"accounts@the-archers.ca",
"young.ngo@gmail.com",
"indexasp@gmail.com",
"ryan@rockfruit.com",
"dev@rmeinl.com",
"matteo.continella@gmail.com",
"zadumpstah@protonmail.com",
"jdoolittle@fastmail.fm",
"martonlanga+qcvc@gmail.com instrument.ts:113 ",
"martonlanga+qcvc@gmail.com",
"parthtejpal@protonmail.com",
"Sergiobhunt@gmail.com",
"andrewsghall@gmail.com",
"andrewwyman27@gmail.com",
"srirambk.dev@gmail.com",
"srirambk.dev@gmail.com",
"lukecannon727@gmail.com",
"Subash12q@gmail.com",
"parker.hawkins92@gmail.com",
"jeff.powell.russell@gmail.com",
"scientist_ken@hotmail.com",
"pavel@artistbridgeconsulting.com",
"gbastin@gmail.com",
"krzyspiekarski@gmail.com",
"michelle.lou.watson@gmail.com",
"mail@marzuk.io",
"kurthian@gmail.com",
"awmarkley@gmail.com",
"watayawataya@web.de",
"acrucettanieto@gmail.com",
"dawid.wiktor.99@gmail.com",
"germansk0@gmail.com",
"wagrobbelaar@gmail.com",
"markbissell21@gmail.com",
"eckstrm@gmail.com",
"njodle@odle.biz",
"embustero1337@gmail.com",
"njodle@odle.biz",
"theemmonk@gmail.com",
"pwsutherland15@gmail.com",
"Ak@summit-media.com",
"gs11xyz@gmail.com",
"Freethinkingaway@gmail.com",
"augustveryown05@gmail.com",
"dnanaq1@hotmail.com",
"marcusdelbianco@yahoo.com",
"ryangale93@gmail.com",
"chris.kleinknecht@gmail.com",
"hoangpqse01968@gmail.com",
"hoangpqse01968@gmail.com",
"pdf19640829@gmail.com",
"amityadav85@outlook.in",
"stephen.t.graham@gmail.com",
"sriharij@gmail.com",
"Jamiekinnear@hotmail.co.uk",
"robert.reihmeier@gmx.de",
"nick@umaproject.org",
"aamir.kajani@gmail.com",
"lourenslinde@gmail.com",
"ingjmj@gmail.com",
"Edwinjoy04@gmail.com",
"Interempty@icloud.com",
"ingjmj@gmail.com",
"mingjie.tan88@gmail.com",
"bhaskarbrhm36@gmaill.com",
"norswap@gmail.com",
"Kyle.cetrulo@auxocell.com",
"Ben@benlevin.com",
"matthewdcannon@gmail.com",
"a.down45@gmail.com",
"yannickleblanc.yl@gmail.com",
"Interempty@icloud.com",
"prajwalvipra@gmail.com",
"amazingperson1700@gmail.com",
"mikeprokofyev@gmail.com",
"mail.la.costi@gmail.com",
"leebetty0630@pm.me",
"andrewjmares@gmail.com",
"Znieh@gmx.net",
"m.spampatti@gmail.com",
"marcin.siewnicki@hotmail.com",
"naimish101.viradia@gmail.com",
"rawasmhd@gmail.com",
"bfinkley10@gmail.com",
"semialoof@gmail.com",
"ric@rmhsilva.com",
"swagenhizzle@protonmail.com",
"humbertogrant@gmail.com",
"humanworking@protonmail.com",
"adnanraza.raza@gmail.com",
"kirill.frusin@gmail.com",
"dpbonner@asu.edu",
"laide@alum.mit.edu",
"butternbeeswax@gmail.com",
"alli.delgizzi@gmail.com",
"matias35v@gmail.com",
"chase.mcdermott12@gmail.com",
"Jfilcik@gmail.com",
"hoangpqse01968@gmail.com",
"t.piechula@outlook.com",
"paolomarin@hotmail.com",
"panditamehul01@gmail.com",
"silviu.apostu@gmail.com",
"sonny.vesali@protonmail.com",
"steve3smith@gmail.com",
"brice@obycode.com",
"pellerin.audree@gmail.com",
"matthew.rieger@gmail.com",
"kylemcmanus63@gmail.com",
"waraimoe@gmail.com",
"will.bamberg@gmail.com",
"rohanaurora@gmail.com",
"jared@puzzleschool.com",
"dbalmain@gmail.com",
"areed1414@gmail.com",
"barthofkin@gmail.com",
"cellzc@gmail.com",
"checkmatetheory@gmail.com",
"justiceisbalance@gmail.com",
"luisdi28ms@gmail.com",
"jasonr0109@gmail.com",
"stephen.t.graham@gmail.com",
"stephen.t.graham@gmail.com",
"tysongaylord@gmail.com",
"ellen2imagine@gmail.com",
"Defranl@gmail.com",
"maximus.audacious@gmail.com",
"colin.dismuke@gmail.com"
]) AS email WITH OFFSET AS entryNumber GROUP BY email),
enteredUsers AS (SELECT userID, timestamp, email, entryNumber FROM enteredUserEmails JOIN `logs.registeredUsers` USING (email))
SELECT * FROM enteredUsers JOIN byUser USING (userID) WHERE readingTime >= 90 ORDER BY entryNumber