Untitled query #12256

SQL

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 > 20150000000000 
      AND rev_user_text LIKE "137.163.%" 
      GROUP BY rev_user_text
) AS edits 
LEFT JOIN 
(
  SELECT 
    SUM(1) AS reverts_c,
    SUBSTR(REGEXP_SUBSTR(r1.rev_comment,'/([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3})'),2) AS reverts_ip
  FROM revision AS r1 
  WHERE 
    r1.rev_comment REGEXP "(ontributions|uokkaukset)/137[.]163[.]" 
   AND r1.rev_timestamp > 20150000000000   
   AND NOT (r1.rev_user=0 
   AND r1.rev_comment LIKE concat("%", r1.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 > 20150000000000 
      AND afl_user_text LIKE "137.163.%" 
   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 "137.163.%" 
     AND log_timestamp > 20150000000000 
   GROUP BY log_title 
) AS blocks 
ON blocks.log_title=rev_user_text 
LEFT JOIN 
ipblocks_ipindex 
ON ipb_address=reverts_ip 
WHERE reverts_ip LIKE "137.163.%" 
GROUP BY ip;
SQL

Checking query status...
