Most active test wikis on Incubator

This query lists the most active test projects on Incubator. It considers everything a test wiki prefix that begins with Wx (x may be any lower case letter), followed by a slash, followed by any number of lower case letters (this is a bit more relaxed than format actually required on Incubator, but the gain of simplicity overweighs the chance of false positives). It takes only main, template, category and module namespaces (and their talk namespaces) into account, per https://incubator.wikimedia.org/wiki/Incubator:Policy#Usage_of_namespaces.

SQL

USE incubatorwiki_p;

SELECT REGEXP_SUBSTR(rc_title, 'W[a-z]/[a-z]+') AS prefix, COUNT(*) AS count
FROM recentchanges
WHERE rc_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) AND rc_type < 5 -- exclude Wikidata edits and category changes
GROUP BY prefix
HAVING prefix <> ''
ORDER BY COUNT(*) DESC;

SQL

Checking query status...