Underreferenced Sports Biographies - All database sources, excludes Lugnuts

Fork of Lugnuts Olympian Biographies Limited - All database sources 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 Article.page_is_redirect = 0
  	AND rev_actor != 482
  	#AND (TalkCat.cl_to LIKE '%iography%')
    AND TalkCat.cl_to = "Sports_and_games_work_group_articles"
  	AND Article.page_len < 3000
),

articledaycount AS (
  SELECT LEFT(rev_timestamp, 8) AS date, COUNT(*) as datecount
	FROM revision_userindex
	JOIN page ON page_id = rev_page
	WHERE rev_parent_id = 0
  		AND page_is_redirect = 0
  		AND rev_actor = 482
  		AND page_namespace = 0
    GROUP BY date
),

contributions AS (
  SELECT articles.page_title, articles.page_id, articles.InitialSize, articles.CurrentSize, articles.rev_timestamp, childRev.rev_len as "ChildRevLen", parentRev.rev_len as "ParentRevLen", childRev.rev_actor
  FROM articles
  LEFT JOIN revision_userindex AS childRev ON page_id = childRev.rev_page
  LEFT JOIN revision AS parentRev ON childRev.rev_parent_id = parentRev.rev_id
  LEFT JOIN change_tag ON childRev.rev_id = ct_rev_id AND ct_tag_id IN (590, 1, 539, 582) #reverted or undo
  WHERE ct_tag_id IS NULL
),

contribution_count AS (
  SELECT page_title, page_id, InitialSize, CurrentSize, CAST(CurrentSize AS SIGNED) - CAST(InitialSize AS SIGNED) as Growth, rev_timestamp, SUM(CASE WHEN CAST(ChildRevLen as SIGNED) - CAST(ParentRevLen as SIGNED) > 300 THEN 1 ELSE 0 END) as ContributorCount
  FROM contributions
  WHERE rev_actor != 482
  GROUP BY page_id
),

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")
)

SELECT page_title, InitialSize, CurrentSize, (CAST(CurrentSize AS SIGNED) - CAST(InitialSize AS SIGNED)) as Growth, rev_timestamp, datecount
FROM contribution_count
JOIN articledaycount ON date = LEFT(rev_timestamp, 8)
WHERE page_id NOT IN (SELECT * FROM reference)
	AND ContributorCount = 0
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...
