Users with most edits in the past month with bots and IPs filtered

Fork of Users with most edits in the past month with bots and IPs filtered by DHN

This query is marked as a draft This query has been published by Anerka.

SQL

WITH groups AS (
  SELECT ug_user, GROUP_CONCAT(ug_group) AS memberships 
  FROM user_groups 
  GROUP BY ug_user)
SELECT
  actor.actor_name AS Kullanıcı,
  memberships AS KullanıcıGrubu,
  COUNT(rev_id) AS DegisiklikSayisi
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(CURDATE(), rev_timestamp) < 30
-- 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 Kullanıcı
-- Giới hạn 200 người có số sửa đổi cao nhất
ORDER BY DegisiklikSayisi DESC
LIMIT 200;

SQL

Checking query status...