Olympian Biographies

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 (TalkCat.cl_to LIKE '%iography%')
  	AND Article.page_len < 2500
),

articlessansmedalists AS (
  SELECT page_title, page_id, rev_timestamp, InitialSize, CurrentSize, cl_to
  FROM articles
  LEFT JOIN categorylinks as ArticleCatExc ON ArticleCatExc.cl_from = page_id 
  	AND ArticleCatExc.cl_to LIKE "Medalists_at_the%"
  WHERE ArticleCatExc.cl_to IS NULL
),

contributions AS (
  SELECT articlessansmedalists.page_title, articlessansmedalists.page_id, articlessansmedalists.InitialSize, articlessansmedalists.CurrentSize, articlessansmedalists.rev_timestamp, childRev.rev_len as "ChildRevLen", parentRev.rev_len as "ParentRevLen", childRev.rev_actor
  FROM articlessansmedalists
  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 ABS(CAST(ChildRevLen as SIGNED) - CAST(ParentRevLen as SIGNED)) > 200 THEN 1 ELSE 0 END) as ContributorCount
  FROM contributions
  GROUP BY page_id
),

reference AS (
  SELECT DISTINCT(page_id)
  FROM articlessansmedalists
  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",
                                  "https://com.sport")
)

SELECT page_title, InitialSize, CurrentSize, (CAST(CurrentSize AS SIGNED) - CAST(InitialSize AS SIGNED)) as Growth, rev_timestamp
FROM contribution_count
WHERE page_id NOT IN (SELECT * FROM reference)
	AND ContributorCount = 1
ORDER BY page_title ASC
SQL

Checking query status...
