Отчет об эффективности обработки флагов модераторов

Создание сообщества Данные и отчетность
1

Это SQL-запрос Data Explorer, предназначенный для получения информации об эффективности и оперативности работы модераторов форума при обработке флагов, поднятых пользователями на сообщениях.

Этот отчет показывает количество обработанных флагов и среднее время разрешения флагов каждым модератором на сайте за указанный период времени.

Понимание того, насколько быстро и эффективно модераторы реагируют на флаги, поможет администраторам обеспечить, чтобы форум оставался безопасным и гостеприимным пространством для всех пользователей.

-- [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 -- время разрешения в минутах
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Типы флагов
      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-запросу

Параметры

Запрос использует два параметра для определения диапазона дат, за который анализируются данные:

  • :start_date: Начальная дата периода, который вы хотите проанализировать, в формате ГГГГ-ММ-ДД.
  • :end_date: Конечная дата периода, который вы хотите проанализировать, также в формате ГГГГ-ММ-ДД.

Пояснение к CTE

  • period_actions: Этот CTE выбирает флаги, поднятые в указанный период времени, с фокусом на определенные типы флагов. Он вычисляет время, затраченное на разрешение каждого флага (в минутах), с момента его создания до тех пор, пока он не был согласован, отклонен или отложен модератором.
  • moderator_actions: Этот CTE фильтрует действия из period_actions, включая только те, на которые отреагировал модератор. Он идентифицирует модератора, ответственного за каждое действие.
  • moderator_stats: Этот CTE агрегирует данные из moderator_actions, вычисляя общее количество флагов, обработанных каждым модератором, и среднее время разрешения (в минутах) для обработанных ими флагов.

Результаты

Итоговый вывод запроса предоставляет список модераторов, отсортированный по количеству обработанных ими флагов, с вторичной сортировкой по среднему времени разрешения (по возрастанию). Для каждого модератора в отчете показывается:

  • moderator_username: Имя пользователя модератора.
  • handled_flags: Общее количество флагов, обработанных модератором в указанный период времени.
  • avg_resolution_time_minutes: Среднее время, затраченное модератором на разрешение флага, округленное до двух знаков после запятой.

Пример результатов

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