Анализ отчетов о модерации и активности по флагом

SaraDev · 13.Февраль.2025 23:08:27

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

Это руководство содержит различные SQL-отчеты для Discourse, предназначенные для анализа деятельности, связанной с модерацией.

В этой теме вы найдете подробные запросы Data Explorer для:

Статистики разрешения флагов.
Процентного соотношения разрешенных элементов для проверки.
Метрик производительности конкретных модераторов.
Внутренней информации об активности пользователей по установке флагов.
Полных данных о всех действиях с пользователями, сообщениями и темами, к которым были применены флаги

Процент разрешения флагов сообщений по типу

Объяснение SQL-запроса

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-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.deleted_at,
           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
),
flag_types AS (
    SELECT pat.id,
           CASE 
               WHEN pat.id = 3 THEN 'off_topic'
               WHEN pat.id = 4 THEN 'inappropriate'
               WHEN pat.id = 6 THEN 'notify_user'
               WHEN pat.id = 7 THEN 'notify_moderators'
               WHEN pat.id = 8 THEN 'spam'
           END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.post_action_type_id
),
resolution_percentages AS (
    SELECT
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT
        flag_type,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN resolution = 'agreed' THEN resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN resolution = 'disagreed' THEN resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN resolution = 'deferred' THEN resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN resolution = 'deleted' THEN resolution_count ELSE 0 END) AS deleted_count,
        MAX(total_flags) AS total_flags
    FROM resolution_percentages
    GROUP BY flag_type
)
SELECT
    flag_type,
    agreed_percentage,
    agreed_count,
    disagreed_percentage,
    disagreed_count,
    deferred_percentage,
    deferred_count,
    deleted_percentage,
    deleted_count,
    total_flags
FROM pivoted_data
ORDER BY flag_type

Используемые параметры

:start_date: Дата начала для фильтрации отмеченных сообщений.
:end_date: Дата окончания для фильтрации отмеченных сообщений.

Объяснение CTE (Common Table Expressions)

period_actions: Фильтрует отмеченные сообщения в указанном диапазоне дат и рассчитывает время до разрешения для каждого флага.
flag_types: Сопоставляет идентификаторы типов флагов с понятными для человека названиями (например, оффтоп, неуместно, спам).
flag_resolutions: Группирует флаги по типу и результату (согласен, не согласен, отложено, удалено) и подсчитывает количество каждого результата.
flag_totals: Рассчитывает общее количество флагов для каждого типа флага.
resolution_percentages: Объединяет количество результатов и общее количество флагов для расчета процента каждого типа результата для каждого типа флага.
pivoted_data: Транспонирует данные для отображения процентов и количества результатов в отдельных столбцах для каждого типа результата.

Объяснение результатов

Итоговый результат представляет собой таблицу, содержащую:

Тип флага (например, оффтоп, спам).
Проценты и количество для каждого типа результата (согласен, не согласен, отложено, удалено).
Общее количество флагов для каждого типа флага.

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

Тип флага	% Согласен	Кол-во Согласен	% Не согласен	Кол-во Не согласен	% Отложено	Кол-во Отложено	% Удалено	Кол-во Удалено	Всего флагов
off_topic	50.00	25	30.00	15	10.00	5	10.00	5	50
spam	70.00	35	20.00	10	5.00	2	5.00	3	50

Проценты разрешения элементов для проверки

Объяснение SQL-запроса

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

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

WITH flag_data AS (
    SELECT
        r.type AS flag_type,
        r.status AS resolution_status,
        COUNT(*) AS flag_count
    FROM
        reviewables r
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
    GROUP BY
        r.type,
        r.status
),
flag_totals AS (
    SELECT
        flag_type,
        SUM(flag_count) AS total_flags
    FROM
        flag_data
    GROUP BY
        flag_type
),
flag_percentages AS (
    SELECT
        fd.flag_type,
        fd.resolution_status,
        fd.flag_count,
        ROUND((fd.flag_count::decimal / ft.total_flags) * 100, 2) AS percentage
    FROM
        flag_data fd
    JOIN
        flag_totals ft
    ON
        fd.flag_type = ft.flag_type
)
SELECT
    fp.flag_type,
    -- Проценты
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.percentage END), 0) AS pending_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.percentage END), 0) AS approved_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.percentage END), 0) AS rejected_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.percentage END), 0) AS ignored_percentage,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.percentage END), 0) AS deleted_percentage,
    -- Количество
    COALESCE(MAX(CASE WHEN fp.resolution_status = 1 THEN fp.flag_count END), 0) AS pending_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 2 THEN fp.flag_count END), 0) AS approved_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 3 THEN fp.flag_count END), 0) AS rejected_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 4 THEN fp.flag_count END), 0) AS ignored_count,
    COALESCE(MAX(CASE WHEN fp.resolution_status = 5 THEN fp.flag_count END), 0) AS deleted_count
FROM
    flag_percentages fp
GROUP BY
    fp.flag_type
ORDER BY
    fp.flag_type

Используемые параметры

:start_date: Дата начала для фильтрации элементов для проверки.
:end_date: Дата окончания для фильтрации элементов для проверки.

Объяснение CTE

flag_data: Группирует элементы для проверки по типу флага и статусу разрешения, подсчитывая количество каждой комбинации.
flag_totals: Рассчитывает общее количество флагов для каждого типа флага.
flag_percentages: Объединяет количество флагов и итоги для расчета процента каждого статуса разрешения для каждого типа флага.

Объяснение результатов

Итоговый результат представляет собой таблицу, содержащую:

Тип флага.
Проценты и количество для каждого статуса разрешения (ожидает, одобрено, отклонено, проигнорировано, удалено).

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

Тип флага	% Ожидает	Кол-во Ожидает	% Одобрено	Кол-во Одобрено	% Отклонено	Кол-во Отклонено	% Прогнорировано	Кол-во Прогнорировано	% Удалено	Кол-во Удалено
off_topic	20.00	10	50.00	25	10.00	5	10.00	5	10.00	5
spam	10.00	5	70.00	35	10.00	5	5.00	2	5.00	3

Разрешения флагов модераторами

Объяснение SQL-запроса

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

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.deleted_at,
        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
),
flag_types AS (
    SELECT 
        pat.id,
        CASE 
            WHEN pat.id = 3 THEN 'off_topic'
            WHEN pat.id = 4 THEN 'inappropriate'
            WHEN pat.id = 6 THEN 'notify_user'
            WHEN pat.id = 7 THEN 'notify_moderators'
            WHEN pat.id = 8 THEN 'spam'
        END AS flag_type
    FROM post_action_types pat
),
flag_resolutions AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        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'
            WHEN pa.deleted_at IS NOT NULL THEN 'deleted'
        END AS resolution,
        COUNT(*) AS resolution_count
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id, resolution
),
flag_totals AS (
    SELECT 
        pa.user_id,
        pa.post_action_type_id,
        COUNT(*) AS total_flags
    FROM period_actions pa
    GROUP BY pa.user_id, pa.post_action_type_id
),
resolution_percentages AS (
    SELECT 
        fr.user_id,
        fty.flag_type,
        fr.resolution,
        fr.resolution_count,
        ft.total_flags,
        ROUND((fr.resolution_count::decimal / ft.total_flags) * 100, 2) AS resolution_percentage
    FROM flag_resolutions fr
    JOIN flag_totals ft ON ft.user_id = fr.user_id AND ft.post_action_type_id = fr.post_action_type_id
    JOIN flag_types fty ON fty.id = fr.post_action_type_id
),
pivoted_data AS (
    SELECT 
        rp.user_id,
        rp.flag_type,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_percentage ELSE 0 END) AS agreed_percentage,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_percentage ELSE 0 END) AS disagreed_percentage,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_percentage ELSE 0 END) AS deferred_percentage,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_percentage ELSE 0 END) AS deleted_percentage,
        MAX(CASE WHEN rp.resolution = 'agreed' THEN rp.resolution_count ELSE 0 END) AS agreed_count,
        MAX(CASE WHEN rp.resolution = 'disagreed' THEN rp.resolution_count ELSE 0 END) AS disagreed_count,
        MAX(CASE WHEN rp.resolution = 'deferred' THEN rp.resolution_count ELSE 0 END) AS deferred_count,
        MAX(CASE WHEN rp.resolution = 'deleted' THEN rp.resolution_count ELSE 0 END) AS deleted_count,
        MAX(rp.total_flags) AS total_flags
    FROM resolution_percentages rp
    GROUP BY rp.user_id, rp.flag_type
)
SELECT 
    u.id AS user_id,
    u.username,
    p.flag_type,
    p.agreed_percentage,
    p.agreed_count,
    p.disagreed_percentage,
    p.disagreed_count,
    p.deferred_percentage,
    p.deferred_count,
    p.deleted_percentage,
    p.deleted_count,
    p.total_flags
FROM pivoted_data p
JOIN users u ON u.id = p.user_id
WHERE (:only_staff = false OR (u.admin = true OR u.moderator = true))
ORDER BY u.username, p.flag_type, p.total_flags

Используемые параметры

:start_date: Дата начала для фильтрации отмеченных сообщений.
:end_date: Дата окончания для фильтрации отмеченных сообщений.
:only_staff: Логический параметр для фильтрации результатов, чтобы включить только сотрудников (администраторов и модераторов).

Объяснение CTE

period_actions: Фильтрует отмеченные сообщения в указанном диапазоне дат и рассчитывает время до разрешения для каждого флага.
flag_types: Сопоставляет идентификаторы типов флагов с понятными для человека названиями.
flag_resolutions: Группирует флаги по пользователю, типу флага и результату, подсчитывая количество каждой комбинации.
flag_totals: Рассчитывает общее количество флагов для каждого пользователя и типа флага.
resolution_percentages: Объединяет количество результатов и итоги для расчета процентов для каждого типа результата.
pivoted_data: Транспонирует данные для отображения процентов и количества результатов в отдельных столбцах для каждого типа результата.

Объяснение результатов

Итоговый результат представляет собой таблицу, содержащую:

Имя пользователя модератора.
Проценты и количество для каждого типа результата (согласен, не согласен, отложено, удалено).
Общее количество флагов, обработанных каждым модератором.

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

Модератор	Тип флага	% Согласен	Кол-во Согласен	% Не согласен	Кол-во Не согласен	% Отложено	Кол-во Отложено	% Удалено	Кол-во Удалено	Всего флагов
mod1	off_topic	60.00	30	20.00	10	10.00	5	10.00	5	50
mod2	spam	70.00	35	20.00	10	5.00	2	5.00	3	50

Кто ставит флаги сообщениям

Объяснение SQL-запроса

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- boolean :only_staff = false

SELECT 
    u.id AS user_id,
    u.username,
    COUNT(pa.id) AS flag_count
FROM post_actions pa
JOIN users u ON u.id = pa.user_id
WHERE pa.post_action_type_id IN (3, 4, 6, 7, 8) -- типы флагов
  AND pa.created_at >= :start_date
  AND pa.created_at <= :end_date
  AND (:only_staff = false OR (u.admin = true OR u.moderator = true))
GROUP BY u.id, u.username
ORDER BY flag_count DESC, u.username
LIMIT 10

Используемые параметры

:start_date: Дата начала для фильтрации отмеченных сообщений.
:end_date: Дата окончания для фильтрации отмеченных сообщений.
:only_staff: Логический параметр для фильтрации результатов, чтобы включить только сотрудников.

Объяснение результатов

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

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

ID пользователя	Имя пользователя	Количество флагов
1	user1	50
2	user2	30
3	user3	20

Заметки о пользователях

Объяснение SQL-запроса

Этот запрос извлекает заметки о пользователях, хранящиеся в таблице plugin_store_rows. Он извлекает такие детали, как ID пользователя, дата создания, содержание заметки и ID создателя.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-01-01

WITH user_notes AS (

    SELECT 
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC 
)

SELECT 
    un.user_id,
    un.created_at::date,
    un.user_note,
    un.created_by AS created_by_user_id
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at::date BETWEEN :start_date AND :end_date
ORDER BY created_at DESC

Используемые параметры

:start_date: Дата начала для фильтрации заметок о пользователях.
:end_date: Дата окончания для фильтрации заметок о пользователях.

Объяснение результатов

Итоговый результат представляет собой подробный список заметок о пользователях с соответствующими метаданными.

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

ID пользователя	Дата создания	Заметка о пользователе	ID пользователя-создателя
1	2025-01-01	Этот пользователь полезен.	2
2	2025-02-01	Этот пользователь связан с двумя другими учетными записями.	3

KPI модераторов - Флаги и среднее время разрешения флагов

Объяснение SQL-запроса

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

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2026-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

Используемые параметры

:start_date: Дата начала для фильтрации отмеченных сообщений.
:end_date: Дата окончания для фильтрации отмеченных сообщений.

Объяснение CTE

period_actions: Фильтрует отмеченные сообщения в указанном диапазоне дат и рассчитывает время до разрешения для каждого флага.
moderator_actions: Определяет флаги, разрешенные модераторами, и рассчитывает время до разрешения для каждого флага.
moderator_stats: Группирует флаги по модераторам и рассчитывает общее количество обработанных флагов и среднее время разрешения.

Объяснение результатов

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

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

Имя пользователя модератора	Обработано флагов	Среднее время разрешения (минуты)
mod1	50	15.00
mod2	30	20.00

Все данные о флагах

Объяснение SQL-запроса

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

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        r.potentially_illegal AS potentially_illegal, 
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        p.hidden_at AS post_hidden_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        --AND r.status = 1 -- Включать только флаги, с которыми согласились и по которым были приняты меры
),
review_decisions AS (
    SELECT
        0 AS status_code, 'pending' AS decision_name
    UNION ALL
    SELECT
        1 AS status_code, 'agreed' AS decision_name
    UNION ALL
    SELECT
        2 AS status_code, 'disagreed' AS decision_name
    UNION ALL
    SELECT
        3 AS status_code, 'ignored' AS decision_name
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'off_topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'illegal' AS flag_type_name
)
SELECT
    fd.flag_id,
    fd.post_id AS flagged_item,
    fd.flagged_by_username,
    fd.flagged_date,
    fd.flag_type,
    ft.flag_type_name AS flag_type_name,
    fd.flag_source AS reviewable_by_moderator,
    fd.flag_reason,
    fd.flagged_item_text,
    pa.related_post_id AS related_message_id_post_id,
    regexp_replace(rp.raw, '(https?://[^\s]+)', '', 'g') AS related_message_text, -- Удаляет только URL-адреса
    fd.reviewed_at,
    fd.reviewed_by_username AS reviewed_by,
    rd.decision_name AS review_decision,
    CASE 
        WHEN fd.user_silenced_till IS NOT NULL THEN 'User silenced'
        WHEN fd.user_suspended_till IS NOT NULL THEN 'User suspended'
        WHEN fd.post_deleted_at IS NOT NULL THEN 'Post deleted'
        WHEN fd.post_hidden_at IS NOT NULL THEN 'Post hidden'
        ELSE 'No action taken'
    END AS action_taken,
    CASE 
        WHEN fd.reviewed_at IS NOT NULL THEN ROUND(EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date)) / 60, 2)
        ELSE NULL
    END AS review_time_minutes -- Разница во времени в минутах
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN posts rp 
    ON pa.related_post_id = rp.id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
LEFT JOIN review_decisions rd 
    ON fd.review_status = rd.status_code
ORDER BY
    fd.flagged_date DESC

Используемые параметры

:start_date: Дата начала для фильтрации отмеченных сообщений.
:end_date: Дата окончания для фильтрации отмеченных сообщений.

Объяснение CTE

flag_data: Извлекает подробную информацию об отмеченных сообщениях, включая ID флага, отмеченный элемент, тип флага, причину флага, источник флага и детали проверки.
review_decisions: Сопоставляет коды статусов проверки с понятными для человека названиями решений (например, ожидает, согласен, не согласен, проигнорировано).
flag_types: Сопоставляет идентификаторы типов действий с сообщениями с понятными для человека названиями типов флагов (например, оффтоп, неуместно, спам).

Объяснение результатов

ID флага: Уникальный идентификатор флага.
Отмеченный элемент: ID отмеченного сообщения.
Имя пользователя, поставившего флаг: Имя пользователя, который поставил флаг сообщению.
Дата установки флага: Дата создания флага.
Тип флага: Числовой тип флага.
Название типа флага: Понятное для человека название типа флага (например, оффтоп, спам).
Проверяется модератором: Указывает, был ли флаг установлен пользователем или системой.
Причина флага: Указанная причина для флага.
Текст отмеченного элемента: Содержание отмеченного сообщения.
ID связанного сообщения: ID любого связанного сообщения (если применимо).
Текст связанного сообщения: Содержание связанного сообщения, с удаленными URL-адресами для ясности.
Имя пользователя проверяющего: Имя пользователя модератора, который проверил флаг.
Решение проверки: Решение, принятое проверяющим (например, согласен, не согласен, проигнорировано, удалено).
Принятые меры: Меры, принятые в результате флага, такие как отключение звука или блокировка пользователя, удаление или скрытие сообщения, или отсутствие действий.
Время проверки (минуты): Время, затраченное на проверку флага, рассчитанное как разница между временем создания флага и временем проверки, в минутах.

Пример результатов (анонимизированные)

ID флага	Отмеченный элемент	Имя пользователя, поставившего флаг	Дата установки флага	Тип флага	Название типа флага	Проверяется модератором	Причина флага	Текст отмеченного элемента	ID связанного сообщения	Текст связанного сообщения	Имя пользователя проверяющего	Решение проверки	Принятые меры	Время проверки (минуты)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Spam content	“Buy now at spam.com”	98765	“Check this out!”	mod456	Agreed	Post deleted	15.25
12346	67891	user124	2025-04-02 14:30	4	Inappropriate	false	Offensive	“This is inappropriate!”	NULL	NULL	mod457	Disagreed	No action taken	30.50

Поданные отчеты о флагах

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

-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2026-01-01

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Something else' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total
FROM
    flag_data fd
LEFT JOIN post_actions pa 
    ON pa.post_id = fd.post_id
LEFT JOIN flag_types ft 
    ON pa.post_action_type_id = ft.post_action_type_id
GROUP BY
    ft.flag_type_name
ORDER BY
    Total DESC

Используемые параметры

:start_date: Дата начала для фильтрации помеченных сообщений.
:end_date: Дата окончания для фильтрации помеченных сообщений.

Объяснение CTE

flag_data:
Этот CTE извлекает подробную информацию о помеченных сообщениях, включая:

Уникальный ID флага (flag_id), ID помеченного сообщения (post_id) и тему, к которой оно относится (topic_id).
Информацию о самом флаге, такую как пользователь, поставивший флаг (flagged_by_username), дата установки флага (flagged_date), тип флага (flag_type) и причина флага (flag_reason).
Детали процесса модерации, включая модератора, рассмотревшего флаг (reviewed_by_username), решение модератора (review_status) и время рассмотрения (reviewed_at).

flag_types:
Этот CTE сопоставляет числовые идентификаторы типов действий с сообщениями с человеко-читаемыми названиями типов флагов:

3: Не по теме
4: Неприемлемый контент
6: Уведомить пользователя
7: Уведомить модераторов
8: Спам
10: Незаконный контент
NULL: Другое

Объяснение результатов

Финальный запрос агрегирует данные о флагах по типу флага и предоставляет следующие метрики:

Тип: Человеко-читаемое название типа флага (например, Не по теме, Спам).
Пожаловались: Количество флагов, поданных пользователями (исключая системные флаги).
Автоматически: Количество флагов, сгенерированных системой или ботами (например, spam_scanner_bot, system).
Всего: Общее количество флагов для каждого типа.

Результаты отсортированы по общему количеству флагов в порядке убывания.

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

Тип	Пожаловались	Автоматически	Всего
Спам	120	80	200
Неприемлемый контент	90	10	100
Не по теме	60	5	65
Уведомить модераторов	30	0	30
Незаконный контент	10	2	12
Другое	5	0	5

Банов и блокировок:

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2026-01-01
SELECT 
    u.id AS user_id,
    u.username,
    u.name,
    u.suspended_at,
    u.suspended_till,
    u.silenced_till,
    u.created_at AS account_created_at,
    u.last_seen_at,
    u.flag_level,
    u.admin,
    u.moderator
FROM 
    users u
WHERE 
    (
        u.suspended_at BETWEEN :start_date AND :end_date
        OR u.silenced_till BETWEEN :start_date AND :end_date
    )
ORDER BY 
    u.suspended_at DESC NULLS LAST,
    u.silenced_till DESC NULLS LAST

Используемые параметры

:start_date: Дата начала для фильтрации заблокированных или приглушенных пользователей.
:end_date: Дата окончания для фильтрации заблокированных или приглушенных пользователей.

Объяснение результатов

Этот запрос извлекает список пользователей, которые были заблокированы или приглушены в указанном диапазоне дат. Ключевые столбцы включают:

ID пользователя: Уникальный идентификатор пользователя.
Имя пользователя: Имя пользователя.
Имя: Полное имя пользователя (если доступно).
Дата блокировки: Дата, когда пользователь был заблокирован.
Блокировка до: Дата, до которой пользователь заблокирован.
Приглушение до: Дата, до которой пользователь приглушен.
Дата создания аккаунта: Дата создания аккаунта пользователя.
Последняя активность: Время последней активности пользователя на платформе.
Уровень флага: Текущий уровень флага пользователя.
Админ: Является ли пользователь администратором (true/false).
Модератор: Является ли пользователь модератором (true/false).

Результаты отсортированы по дате блокировки (suspended_at) и дате приглушения (silenced_till) в порядке убывания, при этом значения NULL отображаются в конце.

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

ID пользователя	Имя пользователя	Имя	Дата блокировки	Блокировка до	Приглушение до	Дата создания аккаунта	Последняя активность	Уровень флага	Админ	Модератор
101	user123	John Doe	2025-03-15 10:00	2025-04-15 10:00	NULL	2020-01-01 12:00	2025-03-14 18:00	2	false	false
102	user456	Jane Smith	NULL	NULL	2025-03-20 18:00	2021-06-10 15:00	2025-03-19 20:00	1	false	false
103	mod789	Moderator1	2025-02-01 08:00	2025-03-01 08:00	NULL	2019-05-05 10:00	2025-01-31 22:00	3	false	true
104	admin001	AdminUser	NULL	NULL	2025-03-25 12:00	2018-12-25 09:00	2025-03-24 16:00	0	true	false

Принятые согласованные действия по флагам

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Включать только флаги, которые были согласованы и по которым были приняты меры
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
median_time_to_act AS (
    SELECT
        COALESCE(ft.flag_type_name, fd.flag_type) AS flag_type,
        ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (fd.reviewed_at - fd.flagged_date))) / 60) AS median_time_minutes
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        fd.reviewed_at IS NOT NULL
        AND (
            ft.flag_type_name IS NOT NULL -- Включать сопоставленные типы флагов
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Включать конкретные типы флагов для результатов NULL
        )
    GROUP BY
        COALESCE(ft.flag_type_name, fd.flag_type)
)
SELECT
    COALESCE(ft.flag_type_name, fd.flag_type) AS Type,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Reported,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automated,
    COUNT(*) AS Total,
    COALESCE(mta.median_time_minutes, 0) AS "Median time to act (minutes)",
    COUNT(CASE WHEN fd.user_silenced_till IS NOT NULL THEN 1 END) AS "User silenced",
    COUNT(CASE WHEN fd.user_suspended_till IS NOT NULL THEN 1 END) AS "User deleted",
    COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS "Post deleted",
    COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS "Post hidden"

FROM
    flag_data fd
LEFT JOIN flag_types ft 
    ON fd.post_action_type_id = ft.post_action_type_id
LEFT JOIN median_time_to_act mta 
    ON COALESCE(ft.flag_type_name, fd.flag_type) = mta.flag_type
WHERE
    ft.flag_type_name IS NOT NULL -- Включать сопоставленные типы флагов
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Включать конкретные типы флагов для результатов NULL
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Используемые параметры

:start_date: Дата начала для фильтрации согласованных флагов.
:end_date: Дата окончания для фильтрации согласованных флагов.

Объяснение CTE

flag_data:
Этот CTE извлекает подробную информацию о флагах, которые были согласованы и по которым были приняты меры. Он включает:

Уникальный ID флага (flag_id), ID помеченного сообщения (post_id) и тему, к которой оно относится (topic_id).
Информацию о самом флаге, такую как пользователь, поставивший флаг (flagged_by_username), дата установки флага (flagged_date), тип флага (flag_type) и причина флага (flag_reason).
Детали процесса модерации, включая модератора, рассмотревшего флаг (reviewed_by_username), решение модератора (review_status) и время рассмотрения (reviewed_at).
Дополнительную информацию о помеченном сообщении, например, было ли оно удалено, скрыто, или был ли автор приглушен или заблокирован.

flag_types:
Этот CTE сопоставляет числовые идентификаторы типов действий с сообщениями с человеко-читаемыми названиями типов флагов:

3: Не по теме
4: Неприемлемый контент
6: Уведомить пользователя
7: Уведомить модераторов
8: Спам
10: Незаконный контент

median_time_to_act:
Этот CTE вычисляет медианное время (в минутах), затраченное на выполнение действий по каждому типу флага. Время рассчитывается как разница между временем создания флага (flagged_date) и временем рассмотрения (reviewed_at).

Объяснение результатов

Финальный запрос агрегирует данные о согласованных флагах по типу флага и предоставляет следующие метрики:

Тип: Человеко-читаемое название типа флага (например, Не по теме, Спам).
Пожаловались: Количество флагов, поданных пользователями (исключая системные флаги).
Автоматически: Количество флагов, сгенерированных системой или ботами (например, spam_scanner_bot, system).
Всего: Общее количество флагов для каждого типа.
Медианное время реакции (минуты): Медианное время, затраченное на выполнение действий по флагам этого типа, в минутах.
Пользователь приглушен: Количество флагов, приведших к приглушению пользователя.
Пользователь удален: Количество флагов, приведших к блокировке пользователя.
Сообщение удалено: Количество флагов, приведших к удалению сообщения.
Сообщение скрыто: Количество флагов, приведших к скрытию сообщения.

Результаты отсортированы по общему количеству флагов в порядке убывания.

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

Тип	Пожаловались	Автоматически	Всего	Медианное время реакции (минуты)	Пользователь приглушен	Пользователь удален	Сообщение удалено	Сообщение скрыто
Спам	100	50	150	30	20	10	50	30
Неприемлемый контент	80	5	85	45	15	5	30	20
Не по теме	40	2	42	25	5	0	10	15
Уведомить модераторов	20	0	20	60	0	0	5	10
Незаконный контент	5	1	6	120	1	1	3	2

Предпринятые действия модерации

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Включать только флаги, которые были согласованы и по которым были приняты меры
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS user_flagged,
        COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS automation_flagged
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL -- Включать сопоставленные типы флагов
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Включать конкретные типы флагов для результатов NULL
),
warnings_issued AS (
    SELECT
        COUNT(*) AS warnings_count
    FROM
        user_warnings
    WHERE
        created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        COUNT(CASE 
            WHEN uh.action = 1 
                AND (
                    LOWER(uh.context) LIKE '%deleted via review queue%' OR
                    LOWER(uh.context) LIKE '%to be a spammer%' OR
                    LOWER(uh.context) LIKE '%review%' OR
                    LOWER(uh.context) LIKE '%reviewable user rejected%'
                ) 
            THEN 1 
        END) AS accounts_deleted,
        COUNT(CASE WHEN uh.action = 10 THEN 1 END) AS accounts_suspended
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
),
posts_deleted_and_hidden AS (
    SELECT
        COUNT(CASE WHEN fd.post_deleted_at IS NOT NULL THEN 1 END) AS posts_deleted,
        COUNT(CASE WHEN fd.post_hidden_at IS NOT NULL THEN 1 END) AS posts_hidden
    FROM
        flag_data fd
),
silences_issued AS (
    SELECT
        COUNT(*) AS silences_count
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    'Content flagged by users' AS category,
    fc.user_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Content flagged by automation' AS category,
    fc.automation_flagged AS "Number of Cases"
FROM flagged_content fc

UNION ALL

SELECT
    'Posts deleted for violating terms' AS category,
    pdh.posts_deleted AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Posts hidden' AS category,
    pdh.posts_hidden AS "Number of Cases"
FROM posts_deleted_and_hidden pdh

UNION ALL

SELECT
    'Warnings Issued' AS category,
    wi.warnings_count AS "Number of Cases"
FROM warnings_issued wi

UNION ALL

SELECT
    'Accounts deleted' AS category,
    vs.accounts_deleted AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Accounts suspended' AS category,
    vs.accounts_suspended AS "Number of Cases"
FROM violations_and_suspensions vs

UNION ALL

SELECT
    'Users silenced for 10+ years' AS category,
    si.silences_count AS "Number of Cases"
FROM silences_issued si

Используемые параметры

:start_date: Дата начала для фильтрации действий модерации.
:end_date: Дата окончания для фильтрации действий модерации.

Объяснение результатов

Запрос агрегирует действия модерации по категориям и предоставляет общее количество случаев для каждой категории. Категории включают:

Контент, помеченный пользователями: Количество сообщений, помеченных обычными пользователями (исключая системные флаги).
Контент, помеченный автоматикой: Количество сообщений, помеченных автоматическими системами или ботами (например, spam_scanner_bot, system).
Сообщения, удаленные за нарушение правил: Количество сообщений, которые были удалены из-за нарушения правил сообщества или условий использования.
Сообщения, скрытые: Количество сообщений, которые были скрыты (но не удалены) по различным причинам.
Выданные предупреждения: Количество предупреждений, выданных пользователям за неуместное поведение или контент.
Удаленные аккаунты: Количество пользовательских аккаунтов, удаленных из-за нарушений, таких как пометка как спамеров или отклонение в очередях модерации.
Заблокированные аккаунты: Количество пользовательских аккаунтов, заблокированных на определенный срок из-за нарушений.
Пользователи, приглушенные на 10+ лет: Количество пользователей, приглушенных бессрочно или на длительный срок (10+ лет).

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

Категория	Количество случаев
Контент, помеченный пользователями	150
Контент, помеченный автоматикой	100
Сообщения, удаленные за нарушение правил	50
Сообщения, скрытые	30
Выданные предупреждения	20
Удаленные аккаунты	10
Заблокированные аккаунты	15
Пользователи, приглушенные на 10+ лет	5

Индивидуальные действия модерации

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

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

WITH flag_data AS (
    SELECT
        r.id AS flag_id,
        p.id AS post_id,
        p.topic_id,
        p.raw AS flagged_item_text,
        p.user_id AS post_author_id,
        fu.username AS flagged_by_username,
        r.created_at AS flagged_date,
        r.type AS flag_type,
        r.reviewable_by_moderator AS flag_source,
        r.payload AS flag_reason,
        r.status AS review_status,
        rs.reviewed_by_id,
        rs.reviewed_at,
        rs.score AS review_score,
        ru.username AS reviewed_by_username,
        p.deleted_at AS post_deleted_at,
        u.silenced_till AS user_silenced_till,
        u.suspended_till AS user_suspended_till,
        p.hidden_at AS post_hidden_at,
        pa.post_action_type_id
    FROM
        reviewables r
    LEFT JOIN posts p ON r.target_id = p.id AND r.target_type = 'Post'
    LEFT JOIN users fu ON r.created_by_id = fu.id
    LEFT JOIN reviewable_scores rs ON rs.reviewable_id = r.id
    LEFT JOIN users ru ON rs.reviewed_by_id = ru.id
    LEFT JOIN users u ON p.user_id = u.id
    LEFT JOIN post_actions pa ON pa.post_id = p.id
    WHERE
        r.created_at BETWEEN :start_date AND :end_date
        AND r.status = 1 -- Включать только флаги, которые были согласованы и по которым были приняты меры
),
flag_types AS (
    SELECT
        3 AS post_action_type_id, 'Off-topic' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inappropriate' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notify_user' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notify_moderators' AS flag_type_name
    UNION ALL
    SELECT
        8 AS post_action_type_id, 'Spam' AS flag_type_name
    UNION ALL
    SELECT
        10 AS post_action_type_id, 'Illegal' AS flag_type_name
),
flagged_content AS (
    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by users' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system')
    
    UNION ALL

    SELECT
        fd.flagged_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.flagged_date AS action_date,
        'Content flagged by automation' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    LEFT JOIN flag_types ft 
        ON fd.post_action_type_id = ft.post_action_type_id
    WHERE
        ft.flag_type_name IS NOT NULL
        AND fd.flagged_by_username IN ('spam_scanner_bot', 'system')
),
posts_deleted_and_hidden AS (
    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_deleted_at AS action_date,
        'Posts deleted for violating terms' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_deleted_at IS NOT NULL
    
    UNION ALL

    SELECT
        fd.reviewed_by_username AS acting_user,
        CAST(fd.post_author_id AS TEXT) AS target_user,
        fd.post_hidden_at AS action_date,
        'Posts hidden' AS category,
        fd.flagged_item_text AS context
    FROM
        flag_data fd
    WHERE
        fd.post_hidden_at IS NOT NULL
),
warnings_issued AS (
    SELECT
        CAST(uw.created_by_id AS TEXT) AS acting_user,
        CAST(uw.user_id AS TEXT) AS target_user,
        uw.created_at AS action_date,
        'Warnings Issued' AS category,
        NULL AS context
    FROM
        user_warnings uw
    WHERE
        uw.created_at BETWEEN :start_date AND :end_date
),
violations_and_suspensions AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts deleted' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 1
        AND (
            LOWER(uh.context) LIKE '%deleted via review queue%' OR
            LOWER(uh.context) LIKE '%to be a spammer%' OR
            LOWER(uh.context) LIKE '%review%' OR
            LOWER(uh.context) LIKE '%reviewable user rejected%'
        )
    
    UNION ALL

    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Accounts suspended' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.created_at BETWEEN :start_date AND :end_date
        AND uh.action = 10
),
silences_issued AS (
    SELECT
        CAST(uh.acting_user_id AS TEXT) AS acting_user,
        CAST(uh.target_user_id AS TEXT) AS target_user,
        uh.created_at AS action_date,
        'Users silenced for 10+ years' AS category,
        uh.context AS context
    FROM
        user_histories uh
    WHERE
        uh.action = 30 -- silence_user
        AND uh.created_at BETWEEN :start_date AND :end_date
        AND EXISTS (
            SELECT 1
            FROM users u
            WHERE u.id = uh.target_user_id 
              AND u.silenced_till > (CAST(:start_date AS TIMESTAMP) + INTERVAL '10 years')
        )
)
SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM flagged_content

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM posts_deleted_and_hidden

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM warnings_issued

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM violations_and_suspensions

UNION ALL

SELECT
    acting_user,
    target_user,
    action_date,
    category,
    context
FROM silences_issued

Используемые параметры

:start_date: Дата начала для фильтрации индивидуальных действий модерации.
:end_date: Дата окончания для фильтрации индивидуальных действий модерации.

Объяснение результатов

Запрос предоставляет подробный журнал индивидуальных действий модерации, включая:

Действующий пользователь: Имя пользователя модератора, системы или пользователя, совершившего действие.
Целевой пользователь: Имя пользователя или ID пользователя, который был объектом действия (например, автор помеченного сообщения или получатель предупреждения).
Дата действия: Дата и время, когда было совершено действие.
Категория: Тип действия модерации, такой как:

Контент, помеченный пользователями
Контент, помеченный автоматикой
Сообщения, удаленные за нарушение правил
Сообщения, скрытые
Выданные предупреждения
Удаленные аккаунты
Заблокированные аккаунты
Пользователи, приглушенные на 10+ лет

Контекст: Дополнительная информация или контент, связанный с действием, например, текст помеченного сообщения или причина блокировки.

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

Действующий пользователь	Целевой пользователь	Дата действия	Категория	Контекст
user123	user456	2024-02-01 10:00	Контент, помеченный пользователями	«Это сообщение содержит спам-контент.»
spam_scanner	user789	2024-02-02 12:00	Контент, помеченный автоматикой	«Обнаружен как спам системой.»
mod001	user456	2024-02-03 14:00	Сообщения, удаленные за нарушение правил	«Пример содержимого сообщения
mod002	user123	2024-02-04 16:00	Сообщения, скрытые	«Сообщение признано неуместным.»
admin001	user789	2024-02-05 18:00	Выданные предупреждения	NULL
admin002	user456	2024-02-06 20:00	Удаленные аккаунты	«Аккаунт удален через очередь модерации.»
mod003	user123	2024-02-07 22:00	Заблокированные аккаунты	«Пользователь заблокирован за повторный спам.»
admin003	user789	2024-02-08 08:00	Пользователи, приглушенные на 10+ лет	«Пользователь приглушен за серьезные нарушения.»

Тема		Ответов	Просм.
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	434	22.12.2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	362	06.02.2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	826	30.10.2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	161	31.05.2025
Show what action was taken and by who for Flags Feature	2	920	12.04.2014

Анализ отчетов о модерации и активности по флагом

Процент разрешения флагов сообщений по типу

Объяснение SQL-запроса

Используемые параметры

Объяснение CTE (Common Table Expressions)

Объяснение результатов

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

Проценты разрешения элементов для проверки

Объяснение SQL-запроса

Используемые параметры

Объяснение CTE

Объяснение результатов

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

Разрешения флагов модераторами

Объяснение SQL-запроса

Используемые параметры

Объяснение CTE

Объяснение результатов

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

Кто ставит флаги сообщениям

Объяснение SQL-запроса

Используемые параметры

Объяснение результатов

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

Заметки о пользователях

Объяснение SQL-запроса

Используемые параметры

Объяснение результатов

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

KPI модераторов - Флаги и среднее время разрешения флагов

Объяснение SQL-запроса

Используемые параметры

Объяснение CTE

Объяснение результатов

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

Все данные о флагах

Объяснение SQL-запроса

Используемые параметры

Объяснение CTE

Объяснение результатов

Пример результатов (анонимизированные)

Пример результатов (анонимизированные)

Поданные отчеты о флагах

Используемые параметры

Объяснение CTE

Объяснение результатов

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

Банов и блокировок:

Используемые параметры

Объяснение результатов

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

Принятые согласованные действия по флагам

Используемые параметры

Объяснение CTE

Объяснение результатов

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

Предпринятые действия модерации

Используемые параметры

Объяснение результатов

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

Индивидуальные действия модерации

Используемые параметры

Объяснение результатов

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

Связанные темы