Olympian biographies by Sander.v.Ginkel

This query produces a list of small Olympian biographies that are created and primarily contain content by Sander.v.Ginkel. This is currently defined as articles with a page length under 4000 with Sander.v.Ginkel contributing more than half the content, but can be adjusted. In addition, redirects, lists and disambiguation pages are excluded.

SQL

WITH creations as (SELECT page_title, page_id, rev_timestamp, page_len, rev_len
FROM revision_userindex
JOIN page ON page_id = rev_page
WHERE rev_parent_id = 0
  AND page_is_redirect = 0
  AND rev_actor = 204363
  AND page_namespace = 0)
  
SELECT DISTINCT contributions.page_title, rev_timestamp, contributions.page_len
FROM (SELECT creations.page_title, creations.page_id, creations.page_len, creations.rev_timestamp, SUM(ABS(CAST(childRev.rev_len as SIGNED) - CAST(parentRev.rev_len as SIGNED))) + creations.rev_len as deltas
  FROM creations
  LEFT JOIN revision_userindex AS childRev ON page_id = childRev.rev_page AND rev_actor = 204363
  LEFT JOIN revision AS parentRev ON childRev.rev_parent_id = parentRev.rev_id
  GROUP BY creations.page_id) AS contributions
LEFT JOIN (SELECT creations.page_id, SUM(ABS(CAST(childRev.rev_len as SIGNED) - CAST(parentRev.rev_len as SIGNED))) as deltas
  FROM creations
  LEFT JOIN revision_userindex AS childRev ON page_id = childRev.rev_page AND rev_actor != 204363
  LEFT JOIN revision AS parentRev ON childRev.rev_parent_id = parentRev.rev_id
  GROUP BY creations.page_id) AS othercontributions ON othercontributions.page_id = contributions.page_id
JOIN categorylinks as artCat ON artCat.cl_from = contributions.page_id AND
  artCat.cl_to = LIKE '%lympic%'
JOIN page AS talk ON contributions.page_title = talk.page_title AND talk.page_namespace = 1
JOIN categorylinks AS talkCat ON talkCat.cl_from = talk.page_id
  AND talkCat.cl_to LIKE '%iography%'
WHERE categoryLinksExclude.cl_to IS NULL
  AND othercontributions.deltas < (contributions.deltas * 2)
  AND page_len < 4000

SQL

Checking query status...