Articles created primarily by Lugnuts

This query produces a list of small articles that are created and primarily contain content by Lugnuts. This is currently defined as articles with a page length under 4000 with Lugnuts 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 = 482
  AND page_namespace = 0)
  
SELECT page_title, rev_timestamp
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 = 482
  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 != 482
  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
LEFT JOIN categorylinks ON cl_from = contributions.page_id AND (
  cl_to = 'Disambiguation_pages' OR
  cl_to LIKE '%disambiguation_pages' OR
  cl_to LIKE 'List%')
WHERE cl_to IS NULL
AND othercontributions.deltas < (contributions.deltas * 2)
AND page_len < 4000

SQL

Checking query status...