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 Plantaest.

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 thành_viên,
  memberships AS nhóm,
  COUNT(rev_id) AS lần_sửa_đổi
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) < 45
-- Lọc ra IP
  AND actor.actor_user IS NOT NULL
-- Lọc ra bot
  AND (groups.memberships IS NULL OR memberships != '' OR LOCATE('bot', memberships) = 0)
-- Group by user.
GROUP BY thành_viên
-- Giới hạn 200 người có số sửa đổi cao nhất
ORDER BY lần_sửa_đổi DESC
LIMIT 350;

SQL

Checking query status...