Autoreviewers' creations, by timerange

Shows the total number of pages in the main namespace created by autopatrolled (internally, 'autoreviewer') users within the past 7, 30, 90, and 180 days.  The timeranges are cumulative (that is, the 30-days total includes pages created within the last 7 days too).

Does not check that the user was already within the autoreviewer group at the time the pages were created.  Nor does it count pages that were created in another namespace (such as User: or Draft:) and then moved to the main namespace.

See also query #76266.

For [[WP:VPI#Patrolling a random sample of edits by autopatrolled editors]] circa 30 Aug 2023.

SQL

SET @minus_7  = DATE_FORMAT(NOW() - INTERVAL 7 DAY, '%Y%m%d%H%i%s');
SET @minus_30 = DATE_FORMAT(NOW() - INTERVAL 30 DAY, '%Y%m%d%H%i%s');
SET @minus_90 = DATE_FORMAT(NOW() - INTERVAL 90 DAY, '%Y%m%d%H%i%s');
SET @minus_180 = DATE_FORMAT(NOW() - INTERVAL 180 DAY, '%Y%m%d%H%i%s');

SELECT COALESCE(actor_name, '--TOTAL--') AS 'name',
       COUNT(CASE WHEN log_timestamp >= @minus_7 THEN 1 ELSE NULL END) AS '7 days',
       COUNT(CASE WHEN log_timestamp >= @minus_30 THEN 1 ELSE NULL END) AS '30 days',
       COUNT(CASE WHEN log_timestamp >= @minus_90 THEN 1 ELSE NULL END) AS '90 days',
       COUNT(CASE WHEN log_timestamp >= @minus_180 THEN 1 ELSE NULL END) AS '180 days'
FROM (SELECT actor_name, log_timestamp
      FROM logging_userindex
      JOIN actor_logging ON actor_id = log_actor
      JOIN user_groups ON ug_user = actor_user
      WHERE ug_group = 'autoreviewer'
        AND log_type = 'create' AND log_action = 'create'
        AND log_timestamp >= @minus_180

      UNION

      -- show empty rows for autopatrolled users with no creations
      SELECT user_name, '0'   -- this timestamp won't be within any of the time cutoffs
      FROM user
      JOIN user_groups ON ug_user = user_id
      WHERE ug_group = 'autoreviewer') subquery
GROUP BY actor_name
WITH ROLLUP;

SQL

Checking query status...