Untitled query #14813

SQL

SELECT 
 year, 
 count(distinct(rev_user_text)) AS c, 
 sum(t1>0) AS tt1, 
 sum(t2>0) AS tt2, 
 sum(t3>0) AS tt3, 
 sum(t4>0) AS tt4 
FROM (
 SELECT
   rev_user_text,
   floor(u.user_registration/10000000000) AS year,
   sum(datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) >3) AS t1,
   sum(datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) > 364 
        AND datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) <730) AS t2,
   sum(datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) > 729 
        AND datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) <1095) AS t3,
   sum(datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) > 1094 
        AND datediff(str_to_date(rev_timestamp, "%Y%m%d%H%i%s"), str_to_date(u.user_registration, "%Y%m%d%H%i%s")) <1460) AS t4

 FROM
   revision_userindex,
   page AS p,
   user AS u
   LEFT JOIN ipblocks ON ipb_user=user_id
 WHERE
   rev_user=user_id
   AND page_id=rev_page
   AND page_namespace=0
   AND rev_comment NOT REGEXP "([Bb]otti.*?(lisäsi|muokkasi|poisti|muutti kielilinkin)|(^|[/ ])iw([:,\[ ]|$)|interwiki|langli)"
   AND rev_comment NOT REGEXP "(ontributions|uokkaukset|^rv|umottu|alautettu|ylätty)"
   AND rev_user >0
   AND ipb_user IS NULL
   AND u.user_name NOT REGEXP "[Bb][o0O][tT7]"
 GROUP BY rev_user 
) AS t 
GROUP BY year;
SQL

Checking query status...
