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

        AND log_namespace = 0

​

      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;

​