Biography search

SQL

SET @PAGELENGTH_MAX = 12500;
SET @PAGE_CREATION_START = "2023-06-01";  # as early as 2021-11-01 is OK
SET @REGISTRATION_START = "2019-06-01";    # as early as 2020-01-01 is OK
SET @PAGE_CREATION_MIN = 2; # only report users who have created at least this many
SET @ARTICLE_SPACE = 0;
SET @TALK_SPACE = 1;
SET @USER_SPACE = 2;
SET @CAT1 = "Unknown-importance Women's sport articles"; 
SET @CAT2 = "Low-importance Women's sport articles";
SET @CAT3 = "Paralympics task force articles";
SET @CAT4 ="All WikiProject Women in Red pages";
SET @CAT1_UNDERSCORE = REPLACE(@CAT1," ","_");
SET @CAT2_UNDERSCORE = REPLACE(@CAT2," ","_");
SET @CAT3_UNDERSCORE = REPLACE(@CAT3," ","_");
SET @CAT4_UNDERSCORE = REPLACE(@CAT4," ","_");

SELECT user_name,count(*) as pages, DATE_FORMAT(max(rev_timestamp), "%Y-%m-%d")  as last_edit
FROM page pageA,revision,categorylinks,page pageB,actor,user
where pageB.page_title = pageA.page_title
and cl_from=pageA.page_id
and (cl_to = @CAT1_UNDERSCORE OR cl_to = @CAT2_UNDERSCORE OR cl_to = @CAT3_UNDERSCORE OR cl_to = @CAT4_UNDERSCORE)
and revision.rev_timestamp >= @PAGE_CREATION_START
and revision.rev_page = pageB.page_id
and revision.rev_parent_id = 0  # NEW PAGE CREATION
and revision.rev_actor = actor.actor_id
and user.user_id = actor.actor_user
and user.user_registration >= @REGISTRATION_START
and pageA.page_namespace = @TALK_SPACE
and pageB.page_namespace = @ARTICLE_SPACE 
and pageB.page_is_redirect = 0
and pageB.page_len < @PAGELENGTH_MAX
and not exists(
    # Filter out blocked users 
    select 1 from ipblocks 
    where user_id=ipb_user # BLOCKED USER
)
and not exists(
  	select 1 from page
  where  page_namespace = @USER_SPACE
  and page_title = user_name
)
and user_name not in ('Walrus World','Balance person','Gab Brasseur','Bayu Oktarino','Sarah.moorhouse',
                      'Otepoti Creative','Yonbik','Reading Beans','SparklyJellyfish',
                      'AMM Pittsburgh','Patricia Mannerheim','Grnrchst','Significa liberdade','Less Unless',
                      'Oughtta Be Otters') # cheap whitelist

group by user_name
HAVING pages >= @PAGE_CREATION_MIN
ORDER BY last_edit desc
limit 100
SQL

Checking query status...
