Articles written by a single editor (tests)

SQL

# TODO
# Group by page_title (subquery?) and get only those whose count is 1

USE enwiki_p;

SELECT page_title, 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 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%'
#GROUP BY (p.page_title)
#HAVING COUNT(r.rev_actor) = 1
LIMIT 100
) AS myalias
GROUP BY (page_title)
SQL

Checking query status...
