User subpages by nonautopatrolled in cswiki - used to filter articles in NS_USER

SQL

use cswiki_p;
select concat("Wikipedista:", page_title), page_len
from page
where page_title like "%/%" # Is a subpage
and page_namespace=2 # Is in NS_USER
and # Exclude autopatrolled, bots and sysops, they should know what they are doing
   replace(REGEXP_REPLACE(page_title, "/.*$", ""), "_", " ")
   not in (select user_name from user where user_id in (select ug_user from user_groups where ug_group in ("autopatrolled", "bot", "sysop")))
and # Exclude users with more than 1000 edits
   replace(REGEXP_REPLACE(page_title, "/.*$", ""), "_", " ") not in (select user_name from user where user_editcount>1000)
and # Exclude users who created at least one article (they should know they can edit there)
   replace(REGEXP_REPLACE(page_title, "/.*$", ""), "_", " ")
   not in (select rev_user_text from revision join page on page_id=rev_page where rev_parent_id=0 and rev_user!=0 and page_namespace=0)
and page_len>600 # Require non-trivial length
and page_title not like "%.js" and page_title not like "%.css" # Exclude scripts
order by page_len desc, page_title asc;
SQL

Checking query status...
