WITH groups AS (

  SELECT ug_user, GROUP_CONCAT(ug_group) AS memberships 

  FROM user_groups 

  GROUP BY ug_user)

SELECT

  actor.actor_name AS anv,

  memberships AS grupper,

  COUNT(rev_id) AS redigeringar

FROM revision LEFT JOIN actor ON (revision.rev_actor = actor.actor_id)

  LEFT JOIN groups ON (actor.actor_user = groups.ug_user)

-- Lọc theo thời gian

WHERE DATEDIFF("2021-01-01", rev_timestamp) > 0 AND DATEDIFF("2021-01-01", rev_timestamp) < 365

​

-- Lọc ra IP

  AND actor.actor_user IS NOT NULL

-- Lọc ra bot

  AND (groups.memberships IS NULL OR LOCATE('bot', memberships) = 0)

-- Group by user.

GROUP BY anv

-- Giới hạn 200 người có số sửa đổi cao nhất

ORDER BY redigeringar DESC

LIMIT 200;