IP edit summary

SQL


SET @timelimit = 20150000000000;
SET @ip_like = "137.163.%";
SET @ip_regexp = "137[.]163[.]";


SELECT 
   SUBSTR(REGEXP_SUBSTR(rev_user_text,'^([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.)'),2) AS ip, 
   COUNT(DISTINCT rev_user_text) AS distinct_addresses_c, 
   AVG(reverts_c/edits_c), 
   SUM(reverts_c) AS reverts, 
   SUM(edits_c) AS edits, 
   SUM(afl_c) AS abusefilter, 
   SUM(block_c) AS estoja, 
   SUM((CASE WHEN ipb_address IS NULL THEN "" ELSE 1 END)) AS estettynä 
FROM 
(
   SELECT 
      SUM(1) AS edits_c,
      rev_user_text,
      rev_page 
   FROM 
      revision_userindex 
   WHERE 
      rev_user=0 
      AND rev_timestamp > @timelimit 
      AND rev_user_text LIKE @ip_like 
      GROUP BY rev_user_text
) AS edits 
LEFT JOIN 
(
  SELECT 
    SUM(1) AS reverts_c,
    SUBSTR(REGEXP_SUBSTR(rev_comment,'/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})'),2) AS reverts_ip
  FROM revision 
  WHERE
   rev_timestamp > @timelimit   
   AND rev_comment LIKE CONCAT("%", @ip_like)
   AND rev_comment REGEXP CONCAT("(ontributions|uokkaukset)/", @ip_regexp) 
   AND NOT (rev_user=0 AND rev_comment LIKE CONCAT("%", rev_user_text, "%")) 
 GROUP BY reverts_ip
) AS reverts 
ON reverts.reverts_ip=edits.rev_user_text 
LEFT JOIN 
(
   SELECT 
      SUM(1) AS afl_c,
      afl_user_text 
   FROM 
      abuse_filter_log  
   WHERE 
      afl_user=0 
      AND afl_timestamp > @timelimit 
      AND afl_user_text LIKE @ip_like 
   GROUP BY afl_user_text
) AS afl 
ON afl.afl_user_text=rev_user_text 
LEFT JOIN 
(
   SELECT 
     SUM(1) AS block_c, 
     log_title 
   FROM 
     logging 
   WHERE 
     log_action="block" 
     AND log_title LIKE @ip_like 
     AND log_timestamp > @timelimit 
   GROUP BY log_title 
) AS blocks 
ON blocks.log_title=rev_user_text 
LEFT JOIN 
ipblocks_ipindex 
ON ipb_address=reverts_ip 
GROUP BY ip;
SQL

Checking query status...
