Articles written by a single editor (test2)

Fork of Articles written by a single editor (posted) by Syced

This query is marked as a draft This query has been published by Syced.

SQL

USE enwiki_p;
SELECT page_title, page_len, COUNT(rev_actor) FROM (
  SELECT p.page_title, r.rev_actor, a.actor_name, page_len
  FROM (
    SELECT page_title, page_len, page_id
    FROM page
    WHERE page_namespace = 0 # Mainspace
      AND NOT page_is_redirect
      AND page_len > 1000 # Avoid most disambiguation pages
    LIMIT 10000
  ) AS p # All mainspace pages
  LEFT JOIN revision_userindex r ON r.rev_page = p.page_id
  LEFT JOIN actor a ON r.rev_actor = a.actor_id
  WHERE NOT IS_IPV4(a.actor_name)
    AND NOT IS_IPV6(a.actor_name)
    AND LOWER(a.actor_name) NOT LIKE '%script%'
    AND LOWER(a.actor_name) NOT LIKE '%bot%'
) AS pra
GROUP BY (page_title)
HAVING COUNT(rev_actor) < 2 # Only 1 editor

SQL

Checking query status...