Отчет по панели управления - статус флагов

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчета панели управления по статусу флагов.

Отчет «Статус флагов» предназначен для предоставления аналитики по процессу модерации путем анализа времени, затраченного на разрешение помеченных сообщений в определенный период. Он включает детали о типе поднятого флага, затронутых сообщениях и пользователях, дате создания флага, статусе разрешения, сотруднике, обработавшем флаг, и времени, необходимом для достижения разрешения.

Этот отчет полезен менеджерам сообществ и модераторам для оценки эффективности команды модерации, выявления любых проблем в процессе разрешения и обеспечения своевременного соблюдения стандартов сообщества.

-- [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
),
poster_data AS (
    SELECT pa.id,
           p.user_id AS poster_id,
           p.topic_id,
           p.post_number,
           u.username_lower AS poster_username,
           u.uploaded_avatar_id AS poster_avatar_id
    FROM period_actions pa
    JOIN posts p ON p.id = pa.post_id
    JOIN users u ON u.id = p.user_id
),
flagger_data AS (
    SELECT pa.id,
           u.id AS flagger_id,
           u.username_lower AS flagger_username,
           u.uploaded_avatar_id AS flagger_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = pa.user_id
),
staff_data AS (
    SELECT pa.id,
           u.id AS staff_id,
           u.username_lower AS staff_username,
           u.uploaded_avatar_id AS staff_avatar_id
    FROM period_actions pa
    JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
    SELECT pat.id,
           pat.id AS flag_type
    FROM post_action_types pat
)
SELECT
    CASE 
        WHEN pat.flag_type = 3 THEN 'off_topic'
        WHEN pat.flag_type = 4 THEN 'inappropriate'
        WHEN pat.flag_type = 6 THEN 'notify_user'
        WHEN pat.flag_type = 7 THEN 'notify_moderators'
        WHEN pat.flag_type = 8 THEN 'spam'
    END "type",
    pa.post_id as flagged_post_id,
    pd.poster_id as poster_user_id,
    fd.flagger_id as flagger_user_id,
    DATE(pa.created_at) as flag_created,
    CASE 
        WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
        WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
        WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
    END "resolution",
    sd.staff_id as assigned_user_id,
    ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC

Пояснение к SQL-запросу

Запрос структурирован с использованием общих табличных выражений (CTE), которые подготавливают подмножество данных, используемое затем в финальном операторе SELECT для формирования отчета.

Параметры

Запрос принимает два параметра:

• :start_date: Начало периода, за который генерируется отчет.
• :end_date: Конец периода, за который генерируется отчет.

CTE

period_actions

Это CTE выбирает действия над сообщениями (флаги), попадающие в указанный диапазон дат и относящиеся к определенным типам (3, 4, 6, 7, 8), которые соответствуют различным причинам выставления флага. Оно вычисляет time_to_resolution_minutes, находя разницу между временем создания флага и временем его реакции (согласовано, отклонено или отложено).

poster_data

Это CTE соединяет period_actions с таблицами posts и users, чтобы получить информацию об авторе помеченного сообщения, включая его ID пользователя, ID темы, номер сообщения и имя пользователя.

flagger_data

Это CTE соединяет period_actions с таблицей users, чтобы получить информацию о пользователе, выставившем флаг, включая его ID пользователя и имя пользователя.

staff_data

Это CTE извлекает информацию о сотруднике, обработавшем флаг, путем соединения period_actions с таблицей users на основе ID сотрудников, которые согласились, отклонили или отложили флаг.

flag_types

Это CTE просто выбирает ID из таблицы post_action_types, которые будут использованы для определения типа флага в финальном операторе SELECT.

Финальный SELECT

Финальный оператор SELECT объединяет все CTE для представления комплексного отчета. Он включает следующие столбцы:

• type: Тип выставленного флага, определяемый по ID flag_type.
• flagged_post_id: ID помеченного сообщения.
• poster_user_id: ID пользователя, опубликовавшего помеченный контент.
• flagger_user_id: ID пользователя, выставившего флаг.
• flag_created: Дата создания флага.
• resolution: Статус разрешения флага (согласовано, отклонено, отложено).
• assigned_user_id: ID сотрудника, обработавшего флаг.
• resolution_time (minutes): Время, затраченное на разрешение флага, в минутах, округленное до двух знаков после запятой.

Отчет затем сортируется по дате создания флага (pa.created_at) в порядке возрастания.

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

type	flagged_post	poster_user	flagger_user	flag_created	resolution	assigned_user	resolution_time (minutes)
off_topic	Example_Post_Link1	example_user1	example_user2	2023-01-01	agreed	example_user3	4.56
inappropriate	Example_Post_Link2	example_user1	example_user2	2023-01-02	disagreed	example_user3	38.76
spam	Example_Post_Link3	example_user1	example_user2	2023-01-03	deferred	example_user3	79.32
—	—	—	—	—	—	—	—