USE fawiki_p;

SELECT

  user_name,REGEXP_REPLACE(GROUP_CONCAT(DISTINCT(ug_group) SEPARATOR ' '),'(uploader|autopatrolled|ipblock\-exempt)','') AS groups,

  COUNT(*) AS active_days

FROM

(

  SELECT DISTINCT

    rev_user,

    LEFT(rev_timestamp, 8) as rev_time

  FROM revision

  WHERE

    rev_timestamp > CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 YEAR), '%Y%m%d'), '000000')

) AS rev_days

left JOIN user

  ON rev_days.rev_user = user_id

left JOIN user_groups

  ON rev_days.rev_user = ug_user

  AND  ug_group NOT IN  ('bot','botadmin')

WHERE

   #ug_user IS NULL

   #AND 

   user_id NOT IN ('374638','285515')# پیام_به_کاربر_جدید and FawikiPatroller

GROUP BY

  rev_user

HAVING

  COUNT(*) > 100

ORDER BY

  rev_days DESC;