Autoreview removals in fiwiki

SQL

#
use fiwiki_p;
select 
	floor(log_timestamp/10000000000) as year, 
    count(distinct(fr_rev_id)) as autoreviews, 
    round(datediff(str_to_date(log_timestamp, "%Y%m%d%H%i%S"), str_to_date(user_registration, "%Y%m%d%H%i%S"))/365,1) as user_age_in_years,
    log_user_text as admin, 
    user_name, 
    log_comment 
from (
  select 
  	user_name, 
  	log_timestamp, 
  	user_id, 
  	user_editcount, 
  	user_registration, 
  	log_user_text, 
  	log_user, 
  	log_comment 
  from 
  	(
    	select 
      		ufg_user 
      	from 
      		user_former_groups 
      	where 
      		ufg_group IN ("editor", "reviewer", "autoreview") 
      	group by ufg_user
    ) as ufg 
  	LEFT JOIN (
      		select 
      			ug_user 
      		from 
      			user_groups 
      		where 
      			ug_group IN ("editor", "reviewer", "autoreview") 
      		group by ug_user
    	) as ug 
  	ON ufg_user=ug_user 
  	LEFT JOIN ipblocks ON ufg_user=ipb_user, 
  	user 
  	LEFT JOIN 
  		(
          	select 
          		log_title, 
          		log_timestamp, 
          		log_user, 
          		log_user_text, 
          		log_params, 
          		log_comment 
         	from 
          		logging_compat 
          	where 
          		log_type="rights" 
         	and ((log_params regexp "oldgroups.*?(reviewer|editor|autoreview).*?newgroups" 
         and log_params not regexp "oldgroups.*?newgroups.*?(reviewer|editor|autoreview)") OR  (log_params REGEXP "(reviewer|editor|autoreview)\n" 
         AND log_params NOT REGEXP "\n(reviewer|editor|autoreview)") ) ) as log ON log_title=replace(user_name, " ", "_")  
  		WHERE 
	  	ug_user IS NULL 
	  	AND ufg_user=user_id 
	  	AND user_name NOT REGEXP "[Bb]ot|Rotlink" 
	  	AND user_name not like "%test%" order by log_timestamp
	) as t 
  LEFT JOIN  flaggedrevs ON t.user_id=fr_user  
  and fr_timestamp<log_timestamp 
  AND fr_flags like "%auto%"  
group by user_name 
order by log_timestamp desc;


-- Columns:
-- - timestamp = day when the autoreview/editor usergroup was removed
-- - autoreviews = number of the autoreviews before usergroup was removed
-- - user_age_in_years = user age when usergroup was removed
-- - admin = admin who removed the usergroup
-- - user_name = user whose usergroup was removed
-- - log_comment = reason for the removal in the log comment
SQL

Checking query status...
