Mallineiden lisäystilasto: perustiedot wikidatassa

SQL

SET @user_name_match = "Ipr1%";	
SET @bot_name_match = "Ipr1Bot";
SET @comment_match = "%takso%";
SET @first_rev_id_to_check = 21339849; -- somewhere early February 2023


SELECT
    WEEK(STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s")) as week,
    count(distinct(r1.rev_page)) as added_templates,
    count(distinct(a3.actor_id)) as editors_before_template_7d,
    count(distinct(a2.actor_id)) as editors_after_template_7d,
    count(distinct(a5.actor_id)) as editors_before_template_14d,
    count(distinct(a4.actor_id)) as editors_after_template_14d    
 FROM 
    page as p,
    actor_revision as a1, 
    comment_revision, 
    revision as r1    
    
-- 7d
    LEFT JOIN revision as r2 ON 
    	r2.rev_page=r1.rev_page 
        AND r2.rev_id>r1.rev_id 
        AND DATEDIFF(STR_TO_DATE(r2.rev_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s")) <8 
    LEFT JOIN actor_revision as a2 ON 
    	r2.rev_actor=a2.actor_id 
        AND a2.actor_name NOT LIKE @user_name_match
    LEFT JOIN revision as r3 ON 
    	r3.rev_page=r1.rev_page 
        AND r3.rev_id<r1.rev_id 
        AND DATEDIFF(STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(r3.rev_timestamp, "%Y%m%d%H%i%s")) <8 
    LEFT JOIN actor_revision as a3 ON 
    	r3.rev_actor=a3.actor_id 
        AND a3.actor_name NOT LIKE @user_name_match
 
-- 14d
    LEFT JOIN revision as r4 ON 
    	r4.rev_page=r1.rev_page 
        AND r4.rev_id>r1.rev_id 
        AND DATEDIFF(STR_TO_DATE(r4.rev_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s")) <15 
    LEFT JOIN actor_revision as a4 ON 
    	r4.rev_actor=a4.actor_id 
        AND a4.actor_name NOT LIKE @user_name_match
    LEFT JOIN revision as r5 ON 
    	r5.rev_page=r1.rev_page 
        AND r5.rev_id<r1.rev_id 
        AND DATEDIFF(STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s"), STR_TO_DATE(r5.rev_timestamp, "%Y%m%d%H%i%s")) <15 
    LEFT JOIN actor_revision as a5 ON 
    	r5.rev_actor=a5.actor_id 
        AND a5.actor_name NOT LIKE @user_name_match
        
        
WHERE 
	r1.rev_comment_id=comment_id 
    AND r1.rev_actor=a1.actor_id 
    AND a1.actor_name=@bot_name_match
    AND r1.rev_id>@first_rev_id_to_check 
    AND r1.rev_page = page_id
    AND page_namespace = 0
    AND comment_text LIKE @comment_match
GROUP BY week
ORDER BY week
SQL

Checking query status...
