Dashboard Report - Moderator Activity

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Moderator Activity.

This report provides a comprehensive overview of the activities performed by moderators within a specified time frame on a Discourse site. The report combines multiple aspects of moderator activity: time spent on the platform, number of flags reviewed, posts created, personal messages (PMs) created, topics created, and post revisions made.

This dashboard report is a valuable tool for administrators looking to measure the effectiveness and engagement of their moderation team, providing a detailed look at their activity and contributions. The insights provided by the report can inform decisions on moderator training, recognition, and recruitment, and ensuring the moderation team is well-balanced and effective in maintaining community standards.

--[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
      )
      
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
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
ORDER BY m.username ASC

Parameters

  • :start_date and :end_date - These parameters define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.
  • :include_admins - This parameter determines whether to include administrators in the report alongside moderators.

SQL Query Explanation

The report is structured using common table expressions (CTEs) to segment the data processing into manageable and logical sections. Here’s what happens in each CTE:

  1. mods: Identifies all users with moderator status or admin status (if included by the :include_admins parameter). It selects only relevant user columns for further queries.
  2. time_read: Calculates the total time (in seconds) each moderator has spent reading content on the platform between the provided start and end dates.
  3. flag_count: Counts the number of flags that moderators have agreed with or disagreed with during the specified period. It takes into account multiple flag types represented by their respective post action type IDs.
  4. revision_count: Counts the number of post revisions made by moderators on other users’ posts within the given timeframe.
  5. topic_count: Counts the number of regular topics created by moderators.
  6. post_count: Counts the number of posts created by moderators in regular topics.
  7. pm_count: Counts the number of private messages initiated by moderators.

After collecting data in the CTEs, the main query joins them based on the user ID and compiles the final report displaying each moderator’s username, the total time spent reading (converted to hours), the number of flags reviewed, topics created, personal messages created, posts created, and revisions made. The results are ordered alphabetically by the moderator’s username.

Example Results

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