Untitled query #85876

SQL

WITH quarry AS (
    SELECT
        a.actor_name AS `User`,
        COUNT(log.log_id) AS `Number of renamings`,
        CONCAT(
            'data-sort-value="', DATE_FORMAT(MAX(log.log_timestamp), '%Y%m%d%H%i%S'), 
            '" | ', DATE_FORMAT(MAX(log.log_timestamp), '%b %d %Y %h:%i %p')
        ) AS `Last action`
    FROM
        actor_user a
    LEFT JOIN
        logging_userindex log
        ON log.log_actor = a.actor_id
        AND log.log_type = 'gblrename'
    GROUP BY
        a.actor_name
    HAVING
        COUNT(log.log_id) > 0
)

SELECT
    q.`User`,
    q.`Number of renamings`,
    q.`Last action`,
    CASE
        WHEN q.`User` IN ('Maintenance script', 'AccountVanishRequests') THEN 'bot'
        ELSE GROUP_CONCAT(DISTINCT CASE
            WHEN ug.ug_group IN ('global-renamer', 'steward') THEN ug.ug_group
            ELSE NULL
        END ORDER BY ug.ug_group ASC SEPARATOR ', ')
    END AS `Roles`
FROM
    quarry q
JOIN
    actor_user a
    ON q.`User` = a.actor_name
JOIN
    user_groups ug
    ON a.actor_user = ug.ug_user
GROUP BY
    q.`User`, q.`Number of renamings`, q.`Last action`
ORDER BY
    q.`Number of renamings` DESC
;
SQL

Checking query status...
