Untitled query #51278

SQL

SET @timestamp=20040000000000;/*first four digits - year; 5,6 digits - month; 7,8 digits - day; 9,10 -hour;11-12 -minutes;13,14 -seconds*/
use tewiki_p;
select 
 	actor_name, 
    pages, 
    edits, 
    (undo_c+rollback_c+flaggedrevs_undo) as reverts, 
 --   undo_c, 
 --   rollback_c, 
 --   flaggedrevs_undo, 
    flaggedrevs_review, 
    blocks, 
    abusefilteredits 
from (
  select 
  	page_namespace, 
	count(distinct(rev_id)) as edits, 
	sum(ctd_name="mw-undo") as undo_c, 
  	sum(ctd_name="mw-rollback") as rollback_c, 
	sum(comment_text LIKE "%Hylättiin viim%") as flaggedrevs_undo,
	count(distinct(page_id)) as pages, 
	floor(rev_timestamp/10000000000) as year, 
	actor_name, 
	r_actor_c as flaggedrevs_review,
  	b_actor_c as blocks,
  	abusefilteredits
from 
  	page, 
	revision_userindex 
	LEFT JOIN change_tag ON ct_rev_id=rev_id
	LEFT JOIN change_tag_def ON ctd_id=ct_tag_id,
	actor_revision 
	LEFT JOIN ( 
      select 
      	actor_id as r_actor_id, 
      	count(distinct(log_id)) as r_actor_c 
      from 
      	logging_userindex, 
      	actor_logging
      where 
      	log_actor=actor_id 
      	and log_action IN ('approve', 'approve2', 'approve-i', 'approve-i2') 
      	and log_type="review" 
      	and log_timestamp >  @timestamp 
      	and actor_name!="SeulojaBot" 
      group by actor_name order by r_actor_c desc limit 500
    ) AS reviews ON r_actor_id=actor_id 
    LEFT JOIN ( 
      select 
      	actor_id as b_actor_id, 
      	count(distinct(log_id)) as b_actor_c 
      from 
      	logging_userindex, 
      	actor_logging 
      where 
      	log_actor=actor_id 
      	and log_action="block" 
      	and log_type="block" 
      	and log_timestamp >  @timestamp 
      group by actor_name 
      order by b_actor_c desc limit 100
    ) as blocks ON b_actor_id=actor_id 
    LEFT JOIN (
      select 
      	sum(1) as abusefilteredits, 
      	afh_user 
      from 
      	abuse_filter_history 
      where 
      	afh_timestamp> @timestamp 
      group by afh_user_text
    ) as afh ON afh_user=actor_user, 
  	comment_revision 
  where 
  	rev_comment_id=comment_id 
  	AND page_id=rev_page 
  	and rev_actor = actor_id 
  	and rev_timestamp> @timestamp 
  	and (actor_name not like "%Bot" AND actor_name not LIKE "%bot") 
  	and page_namespace=0 
  group by actor_name
) as t 
LEFT JOIN (
      select 
      	pl_title as skip_title 
      from 
      	metawiki_p.page, 
      	metawiki_p.pagelinks 
      where 
      	page_namespace=2 
      	and page_title="Emijrp/List_of_Wikimedians_by_number_of_edits/Anonymous" 
      	and pl_from=page_id 
      	and pl_namespace=2
    ) as skip ON REPLACE(skip_title, "_", " ")=actor_name
WHERE skip_title IS NULL
order by reverts desc limit 100
SQL

Checking query status...
