Managing the performance of moderators

Data & reporting
,
4

If anyone is interested I have been using this, which displays the following metrics in a table using the data explorer plug-in:

Moderator_Username
Number of:
post_approved
post_rejected
approve_user
delete_user
suspend_user
unsuspend_user


WITH date_range AS (
  SELECT
    '2024-02-01'::date AS start_date, -- Adjust with the actual start date
    '2024-02-27'::date AS end_date    -- Adjust with the actual end date
)

SELECT
  u.username AS "Moderator_Username",
  COUNT(CASE WHEN uh.action = 56 THEN 1 ELSE NULL END) AS "post_approved",
  COUNT(CASE WHEN uh.action = 64 THEN 1 ELSE NULL END) AS "post_rejected",
  COUNT(CASE WHEN uh.action = 69 THEN 1 ELSE NULL END) AS "approve_user",
  COUNT(CASE WHEN uh.action = 1 THEN 1 ELSE NULL END) AS "delete_user",
  COUNT(CASE WHEN uh.action = 10 THEN 1 ELSE NULL END) AS "suspend_user",
  COUNT(CASE WHEN uh.action = 11 THEN 1 ELSE NULL END) AS "unsuspend_user"
FROM users u
LEFT JOIN user_histories uh ON u.id = uh.acting_user_id
WHERE (uh.created_at BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range))
  AND (u.moderator = true OR u.admin = true)
GROUP BY u.username
ORDER BY u.username

Does anyone have any others they’d like to share?

2 Likes