Revisions per user

SQL

with bots as (
  SELECT ug_user FROM user_groups WHERE ug_group="bot"
)
, actor_revisions as (
SELECT 
r.rev_actor,
a.actor_user IS NOT NULL is_registered_user,
b.ug_user IS NOT NULL is_bot, 
COUNT(1) revisions,
count(case when r.rev_minor_edit then 1 end) minor_revisions
FROM revision_userindex r
LEFT JOIN actor_revision a ON a.actor_id = r.rev_actor
LEFT JOIN bots b ON b.ug_user = a.actor_user 
WHERE rev_timestamp >= 20110000000000
GROUP BY 1,2,3
)
SELECT is_registered_user, is_bot, revisions, minor_revisions, count(1) actors
FROM actor_revisions
GROUP BY 1,2,3,4
SQL

Checking query status...
