SET @oneyear = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -365 DAY), '%Y%m%d%H%i%s');

​

WITH inactive AS

(SELECT actor_id, actor_name

 FROM user_groups

 JOIN actor_user ON actor_user = ug_user

 WHERE ug_group = 'autoreviewer'

   AND NOT EXISTS

   (SELECT 1

    FROM revision_userindex

    WHERE rev_actor = actor_id

      AND rev_timestamp >= @oneyear

    LIMIT 1)

  AND NOT EXISTS

   (SELECT 1

    FROM archive_userindex

    WHERE ar_actor = actor_id

      AND ar_timestamp >= @oneyear

    LIMIT 1)

),

inactive_last(actor_name, rev_max, ar_max) AS

(SELECT actor_name,

        COALESCE((SELECT rev_timestamp

                  FROM revision_userindex

                  WHERE rev_actor = actor_id

                  ORDER BY rev_timestamp DESC

                  LIMIT 1), '0'),

        COALESCE((SELECT ar_timestamp

                  FROM archive_userindex

                  WHERE ar_actor = actor_id

                  ORDER BY ar_timestamp DESC

                  LIMIT 1), '0')

FROM inactive

)

SELECT actor_name, 

       CASE WHEN rev_max >= ar_max

         THEN TO_CHAR(rev_max)

         ELSE CONCAT(TO_CHAR(ar_max), ' (deleted)')

       END AS 'last edit'

FROM inactive_last

ORDER BY IF(rev_max > ar_max, rev_max, ar_max);