Lugnuts BLP Olympic biographies by online source

SQL

WITH RECURSIVE all_cite_temps (page_title, page_id, page_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace
    FROM page
    WHERE page_title = "Citation_templates"
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN all_cite_temps AS parent ON childLinks.cl_to = parent.page_title
    LEFT JOIN page_props ON childLinks.cl_from = pp_page AND pp_propname = "hiddencat"
    WHERE (child.page_namespace = 10 OR child.page_namespace = 14) AND parent.page_namespace = 14 AND pp_propname IS NULL
  )
),

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 TalkCat1 ON TalkCat1.cl_from = Talk.page_id
JOIN categorylinks AS TalkCat2 ON TalkCat2.cl_from = Talk.page_id
JOIN categorylinks AS ArticleCat ON ArticleCat.cl_from = Article.page_id
WHERE rev_parent_id = 0
	AND Article.page_is_redirect = 0
	AND rev_actor = 482
	AND TalkCat1.cl_to = "WikiProject_Biography_articles"
	AND TalkCat2.cl_to = "WikiProject_Olympics_articles"
    AND ArticleCat.cl_to = "Living_people"
	AND Article.page_len < 2500
),

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) > 200 THEN 1 ELSE 0 END) as ContributorCount
  FROM contributions
  WHERE rev_actor != 482
  GROUP BY page_id
),

SELECT el_to_domain_index, COUNT(el_to_domain_index) AS number, page_title AS article_example, el_to_path AS path_example
FROM contribution_count
JOIN externallinks ON page_id = el_from
AND ContributorCount = 0
GROUP BY el_to_domain_index
ORDER by number DESC
SQL

Checking query status...
