- Quarry

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

    UNION ALL

    SELECT
        NULL AS "User",
        COUNT(log.log_id) AS "Number of renamings",
        NULL AS "Last action",
        'steward' 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 = 'steward'
    HAVING
        COUNT(log.log_id) > 0
)

SELECT
    "User",
    "Number of renamings",
    "Last action",
    "Role"
FROM
    quarry
ORDER BY
    "Number of renamings" DESC;
SQL

Checking query status...
