Mallineiden lisäystilasto: perustiedot wikidatassa

SQL

SET @user_name_match = "Ipr1%";	
SET @bot_name_match = "Ipr1Bot";
SET @comment_match = "%taksopa%";


SELECT
    WEEK(STR_TO_DATE(r1.rev_timestamp, "%Y%m%d%H%i%s")) as week,
    count(distinct(r1.rev_id)) as added_templates,
    sum(a3.actor_id IS NOT NULL) as edits_before_template_7d,
    sum(a2.actor_id IS NOT NULL) as edits_after_template_7d
 FROM 
    page as p,
    actor_revision as a1, 
    comment_revision, 
    revision as r1    
    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
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>21339849
    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...
