Articles written by a single editor (tests)

SQL

USE enwiki_p;

SELECT page_title, page_len, COUNT(rev_actor) FROM
(
SELECT DISTINCT p.page_title, p.page_len, r.rev_actor, a.actor_name #COUNT(r.rev_actor) does not work because of the DISTINCT
FROM page p
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 p.page_is_redirect 
AND NOT IS_IPV4(a.actor_name)
AND LOWER(a.actor_name) NOT LIKE '%bot%' # TODO LOWER does not work, I see InternetArchiveBot and AnomieBOT passing.
AND LOWER(a.actor_name) NOT LIKE '%script%'
LIMIT 1000 # Prevent the query from taking too much time
) AS myalias
GROUP BY (page_title)
HAVING COUNT(rev_actor) < 2
ORDER BY page_len DESC
SQL

Checking query status...
