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

Fork of Users with most edits in the past month (including bots and IPs) by DHN

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

SQL

WITH groups AS (
  SELECT ug_user, MIN(user_groups.ug_group)
  FROM user_groups 
  GROUP BY ug_user)
SELECT
  actor.actor_name AS thành_viên,
  ug_group 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) < 30
-- Lọc ra IP
  AND actor.actor_user IS NOT NULL
-- Lọc ra bot
  AND (groups.ug_group IS NULL OR groups.ug_group != 'bot')
-- 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 200;

SQL

Checking query status...