Dashboard Report - Moderator Activity

Data & reporting
, ,
3

Hi @T_Disco,

Yes, to add Posts approved and Posts rejected results to the Moderator Activity report, we can utilize the reviewables and reviewable_scores tables. Specifically, the status field in reviewable_scores can be used to determine whether a post was approved (status = 1) or rejected (status = 2).

Here’s what this would look like added to the report:

Moderator Activity with Posts Approved and Posts Rejected

-- [params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false

WITH mods AS (
    SELECT
        id AS user_id,
        username_lower AS username,
        uploaded_avatar_id
    FROM users u
    WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
    AND u.id > 0
),

time_read AS (
    SELECT SUM(uv.time_read) AS time_read,
        uv.user_id
    FROM mods m
    JOIN user_visits uv ON m.user_id = uv.user_id
    WHERE uv.visited_at >= :start_date
        AND uv.visited_at <= :end_date
    GROUP BY uv.user_id
),
      
flag_count AS (
    WITH period_actions AS (
        SELECT agreed_by_id,
        disagreed_by_id
        FROM post_actions
        WHERE post_action_type_id IN (3,4,8,6,7)
        AND created_at >= :start_date
        AND created_at <= :end_date
    ),

agreed_flags AS (
        SELECT pa.agreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.agreed_by_id = m.user_id
        GROUP BY agreed_by_id
    ),

disagreed_flags AS (
        SELECT pa.disagreed_by_id AS user_id,
        COUNT(*) AS flag_count
        FROM mods m
        JOIN period_actions pa
        ON pa.disagreed_by_id = m.user_id
        GROUP BY disagreed_by_id
    )
    SELECT
    COALESCE(af.user_id, df.user_id) AS user_id,
    COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
    FROM agreed_flags af
    FULL OUTER JOIN disagreed_flags df
    ON df.user_id = af.user_id
      ),
      
revision_count AS (
      SELECT pr.user_id,
      COUNT(*) AS revision_count
      FROM mods m
      JOIN post_revisions pr
      ON pr.user_id = m.user_id
      JOIN posts p
      ON p.id = pr.post_id
      WHERE pr.created_at >= :start_date
      AND pr.created_at <= :end_date
      AND p.user_id <> pr.user_id
      GROUP BY pr.user_id
),
      
topic_count AS (
      SELECT t.user_id,
        COUNT(*) AS topic_count
      FROM mods m
      JOIN topics t ON t.user_id = m.user_id
      WHERE t.archetype = 'regular'
          AND t.created_at >= :start_date
          AND t.created_at <= :end_date
      GROUP BY t.user_id
),
      
post_count AS (
      SELECT p.user_id,
         COUNT(*) AS post_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'regular'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
),
      
pm_count AS (
      SELECT p.user_id,
        COUNT(*) AS pm_count
      FROM mods m
      JOIN posts p ON p.user_id = m.user_id
      JOIN topics t ON t.id = p.topic_id
      WHERE t.archetype = 'private_message'
          AND p.created_at >= :start_date
          AND p.created_at <= :end_date
      GROUP BY p.user_id
),

reviewable_actions AS (
    SELECT
        rs.reviewed_by_id AS user_id,
        SUM(CASE WHEN rs.status = 1 THEN 1 ELSE 0 END) AS posts_approved,
        SUM(CASE WHEN rs.status = 2 THEN 1 ELSE 0 END) AS posts_rejected
    FROM mods m
    JOIN reviewable_scores rs ON rs.reviewed_by_id = m.user_id
    JOIN reviewables r ON r.id = rs.reviewable_id
    WHERE rs.reviewed_at >= :start_date
      AND rs.reviewed_at <= :end_date
    GROUP BY rs.reviewed_by_id
)

SELECT
    m.user_id,
    m.username,
    fc.flag_count as flags_reviewed,
    ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
    tc.topic_count as topics_created,
    pmc.pm_count as PMs_created,
    pc.post_count as posts_created,
    rc.revision_count as revisions,
    ra.posts_approved,
    ra.posts_rejected
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
LEFT JOIN reviewable_actions ra ON ra.user_id = m.user_id
ORDER BY m.username ASC

Where the results for this report would look like:

user username flags_reviewed time_reading_hours topics_created pms_created posts_created revisions posts_approved posts_rejected
1 moderator1 NULL 36.11 NULL 344 8 15 10 5
2 moderator2 46 104.52 2 271 466 363 7 3
3 moderator3 NULL 72.15 NULL 418 64 16 NULL NULL
1 Like