Untitled query #85876

SQL

WITH Quarry AS (
  
    SELECT
        a.actor_name AS 'User',
        COUNT(log.log_id) AS 'Number of renamings',
        MAX(log.log_timestamp) AS 'Last action',
        CASE 
            WHEN ug.ug_group IN ('steward', 'global-renamer') THEN ug.ug_group 
            ELSE NULL 
        END AS 'Role'
    FROM
        user_groups ug
    JOIN
        actor_user a
        ON a.actor_user = ug.ug_user
    LEFT JOIN
        logging_userindex log
        ON log.log_actor = a.actor_id
        AND log.log_type = 'gblrename'
    GROUP BY
        a.actor_name, ug.ug_group
    HAVING
        COUNT(log.log_id) > 0

    UNION ALL

    SELECT
        NULL AS 'User',
        COUNT(log.log_id) AS 'Number of renamings',
        NULL AS 'Last action',
        'global-renamer' AS 'Role'
    FROM
        logging_userindex log
    JOIN
        user_groups ug
        ON log.log_actor = ug.ug_user
    WHERE
        log.log_type = 'gblrename'
        AND ug.ug_group = 'global-renamer'
    HAVING
        COUNT(log.log_id) > 0
)

SELECT DISTINCT
    User,
    'Number of renamings',
    'Last action',
    'Role'
FROM
    Quarry
ORDER BY
    'Number of renamings' DESC;
SQL

Checking query status...
