Moderator Flag Handling Efficiency Report

Data & reporting
1

This is a Data Explorer SQL query designed to provide insights into the efficiency and responsiveness of forum moderators in handling flags raised by users on posts.

This report shows a count of the number of flags handled and the average time to resolve flags by each moderator on a site, over a specified date range.

Understanding how quickly and effectively moderators respond to flags can help administrators ensure that the forum remains a safe and welcoming space for all users.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH period_actions AS (
    SELECT pa.id,
           pa.post_action_type_id,
           pa.created_at,
           pa.agreed_at,
           pa.disagreed_at,
           pa.deferred_at,
           pa.agreed_by_id,
           pa.disagreed_by_id,
           pa.deferred_by_id,
           pa.post_id,
           pa.user_id,
           COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
           EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- time to resolution in minutes
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Flag types
      AND pa.created_at >= :start_date
      AND pa.created_at <= :end_date
),
moderator_actions AS (
    SELECT pa.id,
           pa.post_id,
           pa.created_at,
           pa.responded_at,
           pa.time_to_resolution_minutes,
           COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) AS moderator_id
    FROM period_actions pa
    WHERE COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id) IS NOT NULL
),
moderator_stats AS (
    SELECT
        m.moderator_id,
        u.username AS moderator_username,
        COUNT(m.id) AS handled_flags,
        AVG(m.time_to_resolution_minutes) AS avg_resolution_time_minutes
    FROM moderator_actions m
    JOIN users u ON u.id = m.moderator_id
    GROUP BY m.moderator_id, u.username
)
SELECT
    ms.moderator_username,
    ms.handled_flags,
    ROUND(ms.avg_resolution_time_minutes::numeric, 2) AS avg_resolution_time_minutes
FROM moderator_stats ms
ORDER BY ms.handled_flags DESC, ms.avg_resolution_time_minutes ASC

SQL Query Explanation

Parameters

The query uses two parameters to define the date range for which the data is analyzed:

  • :start_date: The start date of the period you want to analyze, in YYYY-MM-DD format.
  • :end_date: The end date of the period you want to analyze, also in YYYY-MM-DD format.

CTEs Explained

  • period_actions: This CTE selects flags raised within the specified time period, focusing on specific flag types. It calculates the time taken to resolve each flag (in minutes) from the moment it was created until it was either agreed upon, disagreed with, or deferred by a moderator.
  • moderator_actions: This CTE filters the actions from period_actions to include only those that were responded to by a moderator. It identifies the moderator responsible for each action.
  • moderator_stats: This CTE aggregates the data from moderator_actions, calculating the total number of flags handled by each moderator and the average resolution time (in minutes) for the flags they handled.

Results

The final output of the query provides a list of moderators sorted by the number of flags they have handled, with secondary sorting by their average resolution time (in ascending order). For each moderator, the report shows:

  • moderator_username: The username of the moderator.
  • handled_flags: The total number of flags handled by the moderator within the specified time period.
  • avg_resolution_time_minutes: The average time taken by the moderator to resolve a flag, rounded to two decimal places.

Example Results

moderator_username handled_flags avg_resolution_time_minutes
Moderator1 343 39.41
Moderator2 70 30.51
Moderator3 63 42.21