trwiki: users by edit count in main namespace last month

SQL

#set @interval = 30;  -- in days
/*
select
	rev_actor,
    count(*)
from revision
join page on rev_page = page_id
where
    page_namespace = 0 and
    revision.rev_timestamp >= 20220822000000 and
    rev_actor not in (select ug_user from user_groups where ug_group = 'bot')
group by rev_actor
order by count(*) desc

#set @interval = 30;  -- in days

select
	actor_name, ug_group, actor_user, user_id, rev_actor,
    count(*)
from revision, actor, page, user_groups, user
where
	actor_user = user_id and
	rev_page = page_id and
    page_namespace = 0 and
    revision.rev_timestamp >= 20220921000000 and
    actor_user not in (select ug_user from user_groups where ug_group = 'bot')
    
group by rev_actor
order by count(*) desc

/*
select distinct
	actor_name, actor_user, user_id, user_name, rev_actor
    from actor, user_groups, user, revision
where actor_user = user_id
#user_name = actor_name 
# and    
limit 5;
*/

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)
  JOIN page ON (revision.rev_page = page.page_id)
-- Date
WHERE DATEDIFF(CURDATE(), rev_timestamp) < 30
-- IPs out
  AND actor.actor_user IS NOT NULL
-- Bots out
  AND (groups.memberships IS NULL OR LOCATE('bot', memberships) = 0)
-- Mainspace
  AND (page.page_namespace=0)
-- Not too new not too old 
  AND (user.user_editcount > 150 AND user.user_editcount < 7500 )
-- Group by user.
GROUP BY Kullanıcı
-- 
ORDER BY DegisiklikSayisi DESC
LIMIT 200;

/*
SELECT DISTINCT actor_name, rev_actor, COUNT(*)
FROM
	revision
    JOIN page ON rev_page = page_id
    JOIN actor ON rev_actor = actor_id
WHERE
	page_namespace = 0 AND
    revision.rev_timestamp >= 20220822000000 AND
    actor_user not in (select ug_user from user_groups where ug_group = 'bot') 
group by rev_actor
order by count(*) desc
*/
SQL

Checking query status...
