USE fawiki_p;

SELECT

  user_name,GROUP_CONCAT(DISTINCT(ug_group) SEPARATOR ' ') AS groups,

  COUNT(*) AS active_days

FROM

(

  SELECT DISTINCT

    rev_user,

    LEFT(rev_timestamp, 8)

  FROM revision

  WHERE

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

) AS rev_days

JOIN user

  ON rev_user = user_id

LEFT JOIN user_groups

  ON user_id = 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

  active_days DESC;