SELECT user_name, 

       user_editcount, 

       user_registration, 

       MAX(rev_timestamp), 

       GROUP_CONCAT(DISTINCT ug1.ug_group) AS 'user_groups'

FROM user

LEFT JOIN user_groups ug1

    ON user_id = ug1.ug_user 

JOIN actor

    ON actor_user = user_id

JOIN revision_userindex

    ON rev_actor = actor_id

WHERE (user_name LIKE 'Renamed%' 

    OR user_name LIKE 'Vanished%')

    AND ug1.ug_group IS NOT NULL

GROUP BY user_name

HAVING COUNT(DISTINCT ug1.ug_group) > 1 

       OR NOT FIND_IN_SET('extendedconfirmed', GROUP_CONCAT(DISTINCT ug1.ug_group))

ORDER BY MAX(rev_timestamp) DESC;