WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, '(main)'), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,

  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),

  (11,  'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,

  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829,

  'Module talk:'), (2300, 'Gadget:'), (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:'))

SELECT ns_s,

       rc_title,

       actor_name,

       COUNT(*),

       CAST(MAX(rc_timestamp) AS DATE) AS 'most recent'

FROM recentchanges_userindex

LEFT JOIN ns ON ns_n = rc_namespace

JOIN actor_recentchanges ON actor_id = rc_actor

JOIN user_groups ON ug_user = actor_user

JOIN change_tag ON ct_rc_id = rc_id

JOIN change_tag_def ON ctd_id = ct_tag_id

WHERE ug_group = 'bot'

  AND ctd_name = 'mw-manual-revert'

GROUP BY rc_namespace, rc_title, actor_name

HAVING COUNT(*) >= 10

ORDER BY actor_name ASC, COUNT(*) ASC;