Untitled query #79330

SQL

SELECT
  actor_name,
  GROUP_CONCAT(DISTINCT(ug_group) SEPARATOR '، ') AS groups,
  edits
FROM
(
  SELECT
    COUNT(rev_id) AS edits
  FROM
  (
    SELECT DISTINCT
      actor_user,
      actor_name,
      LEFT(rev_timestamp, 8)
    FROM revision
    JOIN actor
      ON rev_actor = actor_id
    WHERE
      rev_timestamp > CONCAT(
        DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH), '%Y%m%d'),
        '000000'
      )
  ) AS rev_days
  LEFT JOIN user_groups
    ON actor_user = ug_user
    AND ug_group = 'bot'
  WHERE
    ug_user IS NULL
    AND actor_user NOT IN (
      30738, /* پیغامبر خوش آمدید */
      91472 /* مقطر غلط کاری */
    )
  GROUP BY
    actor_user
  HAVING
    COUNT(*) > 100
) as users
LEFT JOIN user_groups
  ON actor_user = ug_user
  AND ug_group NOT IN (
    'uploader',
    'autopatrolled',
    'ipblock-exempt'
  )
GROUP BY
  actor_user
ORDER BY
  active_days DESC
SQL

Checking query status...
