Underreferenced Sports Biographies - All database sources, by Lugnuts

Fork of Underreferenced Sports Biographies - All database sources, excludes Lugnuts by BilledMammal
This query is marked as a draft This query has been published by BilledMammal.
SQL

WITH articles AS (
  SELECT DISTINCT(Article.page_title), Article.page_id, rev_timestamp, rev_len as "InitialSize", Article.page_len as "CurrentSize"
FROM revision_userindex
JOIN page AS Article ON Article.page_id = rev_page AND Article.page_namespace = 0 
JOIN page AS Talk ON Article.page_title = Talk.page_title AND Talk.page_namespace = 1
JOIN categorylinks AS ArticleCat ON ArticleCat.cl_from = Article.page_id
JOIN categorylinks AS TalkCat ON TalkCat.cl_from = Talk.page_id
WHERE rev_parent_id = 0
  	AND rev_actor = 482
	AND Article.page_is_redirect = 0
    AND TalkCat.cl_to = "Sports_and_games_work_group_articles"
),

reference AS (
  SELECT DISTINCT(page_id)
  FROM articles
  JOIN externallinks ON el_from = page_id
  WHERE LEFT(el_index,17) NOT IN ("http://com.google", 
                                  "https://org.jstor", 
                                  "https://com.googl", 
                                  "https://org.wikid", 
                                  "https://org.archi",
                                  "https://org.olymp",
                                  "http://org.olympi",
                                  "https://com.sport",
                                  "https://com.deepl",
                                  "https://goog.tran")
  AND LEFT(el_index,20) NOT IN ("https://com.olympics",
                                "https://org.iaaf.www",
                                "https://com.worldrow",
                                "https://org.viaf./vi",
                                "https://com.procycli",
                                "https://org.worldcat",
                                "http://com.cyclingar",
                                "https://org.worldath",
                                "https://cz.olympijsk",
                                "https://se.sok./idro",
                                "https://com.fiba.arc",
                                "https://org.uww.what",
                                "https://com.basketba",
                                "https://com.rio2016.",
                                "https://org.isni./is",
                                "https://net.worldfoo",
                                "https://com.fis-ski.",
                                "https://info.d-nb./g",
                                "https://au.com.olymp",
                                "https://com.national",
                                "https://com.worldaqu",
                                "https://gov.loc.id./",
                                "https://fr.bnf.data.",
                                "https://fr.bnf.catal",
                                "https://com.judoinsi",
                                "https://net.swimrank",
                                "http://com.elitepros",
                                "https://fr.idref.www",
                                "http://hu.olimpia.ar",
                                "http://com.speedskat",
                                "https://com.elitepro",
                                "https://eu.speedskat",
                                "https://com.boxrec./",
                                "https://org.teamengl",
                                "https://com.thecgf./",
                                "https://info.speedsk",
                                "https://com.biathlon",
                                "https://org.issf-spo",
                                "https://ca.olympic./",
                                "https://cz.nkp.aleph",
                                "http://nl.bibliothek",
                                "http://pl.org.bn.mak",
                                "https://com.pyeongch",
                                "https://org.fie./ath",
                                "https://nl.cyclebase",
                                "https://com.proballe",
                                "https://com.itftenni",
                                "https://fr.athle.bas",
                                "https://io.matchid.d",
                                "http://com.london201",
                                "https://com.eurohock",
                                "http://com.omegatimi",
                                "http://com.eurohocke",
                                "https://de.deutsche-",
                                "http://org.olympedia",
                                "http://org.worldcat.",
                                "https://org.fei.www.",
                                "https://com.european",
                                "http://il.org.nli.ul",
                                "https://es.coe.web./",
                                "https://com.realgm.b",
                                "https://eu.footballd",
                                "https://org.ibsf.www")
  OR left(el_index,33) IN ("https://com.google.www./newspaper",
                           "http://com.google.www./newspapers")
                           
)

SELECT page_title, CurrentSize, rev_timestamp
FROM articles
WHERE page_id NOT IN (SELECT * FROM reference)
ORDER BY page_title ASC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
