Bots by number of edits on skwiki (30d, 1y, 10y)

SQL

SELECT
    a.actor_name, 
    COUNT(*) as edit_count_30d
  FROM
    page p, revision r, actor a
  WHERE
    a.actor_id = r.rev_actor and
    r.rev_page = p.page_id and
    -- r.rev_parent_id = 0 and     -- creations only
    -- p.page_namespace = 0 and    -- NS0 only
    -- p.page_is_redirect = 0 and  -- excl. redirects
    (r.rev_timestamp > DATE_SUB(NOW(), INTERVAL 30 DAY)) and
    (a.actor_user in (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') or a.actor_name in ('CommonsDelinker', 'Xqbot', 'InternetArchiveBot'))
  GROUP BY
    actor_name
  ORDER BY
    edit_count_30d desc
  ;

SELECT
    a.actor_name, 
    COUNT(*) as edit_count_1y
  FROM
    page p, revision r, actor a
  WHERE
    a.actor_id = r.rev_actor and
    r.rev_page = p.page_id and
    -- r.rev_parent_id = 0 and     -- creations only
    -- p.page_namespace = 0 and    -- NS0 only
    -- p.page_is_redirect = 0 and  -- excl. redirects
    (r.rev_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)) and
    (a.actor_user in (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') or a.actor_name in ('CommonsDelinker', 'Xqbot', 'InternetArchiveBot'))
  GROUP BY
    actor_name
  ORDER BY
    edit_count_1y desc
  ;

SELECT
    a.actor_name, 
    COUNT(*) as edit_count_10y
  FROM
    page p, revision r, actor a
  WHERE
    a.actor_id = r.rev_actor and
    r.rev_page = p.page_id and
    -- r.rev_parent_id = 0 and     -- creations only
    -- p.page_namespace = 0 and    -- NS0 only
    -- p.page_is_redirect = 0 and  -- excl. redirects
    (r.rev_timestamp > DATE_SUB(NOW(), INTERVAL 10 YEAR)) and
    (a.actor_user in (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') or a.actor_name in ('CommonsDelinker', 'Xqbot', 'InternetArchiveBot'))
  GROUP BY
    actor_name
  ORDER BY
    edit_count_10y desc
  ;
SQL

Checking query status...
