Análisis de informes de moderación y actividad de marcado

SaraDev · 13 Febrero, 2025 23:08

Mantener una comunidad saludable e inclusiva requiere una moderación eficaz, que puede incluir la revisión de publicaciones marcadas, el análisis del rendimiento de los moderadores y la gestión del contenido de los usuarios.

Esta guía contiene una variedad de informes SQL para Discourse diseñados para ayudar a analizar las actividades relacionadas con la moderación.

En este tema encontrarás consultas detalladas de Data Explorer para:

Estadísticas de resolución de banderas.
Porcentajes de resolución de elementos revisables.
Métricas de rendimiento específicas de los moderadores.
Información sobre la actividad de marcado de usuarios.
Datos completos de todas las acciones de usuario, publicación y tema marcadas

Porcentaje de resolución de banderas de publicaciones por tipo

Explicación de la consulta SQL

Esta consulta calcula el porcentaje de resoluciones (aceptadas, rechazadas, diferidas, eliminadas) para las publicaciones marcadas, agrupadas por tipo de bandera.

-- [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 -- tiempo hasta la resolución en minutos
    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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones marcadas.
:end_date: La fecha de fin para filtrar las publicaciones marcadas.

Explicación de las CTE

period_actions: Filtra las publicaciones marcadas dentro del rango de fechas especificado y calcula el tiempo hasta la resolución para cada bandera.
flag_types: Asigna los IDs de tipo de bandera a nombres legibles por humanos (por ejemplo, fuera de tema, inapropiado, spam).
flag_resolutions: Agrupa las banderas por tipo y resolución (aceptada, rechazada, diferida, eliminada) y cuenta las ocurrencias de cada resolución.
flag_totals: Calcula el número total de banderas para cada tipo de bandera.
resolution_percentages: Combina las cuentas de resolución y el total de banderas para calcular el porcentaje de cada tipo de resolución para cada tipo de bandera.
pivoted_data: Pivota los datos para mostrar los porcentajes y recuentos de resolución en columnas separadas para cada tipo de resolución.

Explicación de los resultados

El resultado final es una tabla que muestra:

Tipo de bandera (por ejemplo, fuera de tema, spam).
Porcentajes y recuentos para cada tipo de resolución (aceptada, rechazada, diferida, eliminada).
Total de banderas para cada tipo de bandera.

Ejemplo de resultados

Tipo de bandera	% Aceptadas	Recuento Aceptadas	% Rechazadas	Recuento Rechazadas	% Diferidas	Recuento Diferidas	% Eliminadas	Recuento Eliminadas	Total de banderas
fuera_de_tema	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

Porcentajes de resolución de elementos revisables

Explicación de la consulta SQL

Esta consulta analiza los estados de resolución de los elementos revisables (por ejemplo, publicaciones marcadas) dentro de un rango de fechas dado. Calcula el porcentaje y el recuento de cada estado de resolución para cada tipo de bandera.

-- [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,
    -- Porcentajes
    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,
    -- Recuentos
    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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar los elementos revisables.
:end_date: La fecha de fin para filtrar los elementos revisables.

Explicación de las CTE

flag_data: Agrupa los elementos revisables por tipo de bandera y estado de resolución, contando las ocurrencias de cada combinación.
flag_totals: Calcula el número total de banderas para cada tipo de bandera.
flag_percentages: Combina los recuentos de banderas y los totales para calcular el porcentaje de cada estado de resolución para cada tipo de bandera.

Explicación de los resultados

El resultado final es una tabla que muestra:

Tipo de bandera.
Porcentajes y recuentos para cada estado de resolución (pendiente, aprobado, rechazado, ignorado, eliminado).

Ejemplo de resultados

Tipo de bandera	% Pendiente	Recuento Pendiente	% Aprobado	Recuento Aprobado	% Rechazado	Recuento Rechazado	% Ignorado	Recuento Ignorado	% Eliminado	Recuento Eliminado
fuera_de_tema	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

Resoluciones de banderas de moderadores

Explicación de la consulta SQL

Esta consulta proporciona información sobre la actividad de los moderadores mostrando qué moderadores resolvieron publicaciones marcadas, los tipos de banderas que manejaron y las resoluciones que aplicaron. Calcula porcentajes y recuentos para cada tipo de resolución.

-- [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 -- tiempo hasta la resolución en minutos
    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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones marcadas.
:end_date: La fecha de fin para filtrar las publicaciones marcadas.
:only_staff: Un parámetro booleano para filtrar los resultados e incluir solo miembros del personal.

Explicación de las CTE

period_actions: Filtra las publicaciones marcadas dentro del rango de fechas especificado y calcula el tiempo hasta la resolución para cada bandera.
flag_types: Asigna los IDs de tipo de bandera a nombres legibles por humanos.
flag_resolutions: Agrupa las banderas por usuario, tipo de bandera y resolución, contando las ocurrencias de cada combinación.
flag_totals: Calcula el número total de banderas para cada usuario y tipo de bandera.
resolution_percentages: Combina los recuentos de resolución y los totales para calcular los porcentajes para cada tipo de resolución.
pivoted_data: Pivota los datos para mostrar los porcentajes y recuentos de resolución en columnas separadas para cada tipo de resolución.

Explicación de los resultados

El resultado final es una tabla que muestra:

Nombre de usuario del moderador.
Porcentajes y recuentos para cada tipo de resolución (aceptada, rechazada, diferida, eliminada).
Total de banderas manejadas por cada moderador.

Ejemplo de resultados

Moderador	Tipo de bandera	% Aceptadas	Recuento Aceptadas	% Rechazadas	Recuento Rechazadas	% Diferidas	Recuento Diferidas	% Eliminadas	Recuento Eliminadas	Total de banderas
mod1	fuera_de_tema	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

Quién está marcando publicaciones

Explicación de la consulta SQL

Esta consulta identifica a los usuarios que marcaron publicaciones dentro de un rango de fechas especificado y calcula el número total de banderas enviadas por cada usuario.

-- [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) -- tipos de bandera
  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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones marcadas.
:end_date: La fecha de fin para filtrar las publicaciones marcadas.
:only_staff: Un parámetro booleano para filtrar los resultados e incluir solo miembros del personal.

Explicación de los resultados

El resultado final es una lista clasificada de usuarios con sus recuentos de banderas.

Ejemplo de resultados

ID de usuario	Nombre de usuario	Recuento de banderas
1	user1	50
2	user2	30
3	user3	20

Notas de usuario

Explicación de la consulta SQL

Esta consulta recupera las notas de usuario almacenadas en la tabla plugin_store_rows. Extrae detalles como el ID del usuario, la fecha de creación, el contenido de la nota y el ID del creador.

-- [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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las notas de usuario.
:end_date: La fecha de fin para filtrar las notas de usuario.

Explicación de los resultados

El resultado final es una lista detallada de notas de usuario con metadatos relevantes.

Ejemplo de resultados

ID de usuario	Fecha de creación	Nota de usuario	ID de usuario creador
1	2025-01-01	Este usuario es útil.	2
2	2025-02-01	Este usuario está asociado con dos otras cuentas de usuario.	3

KPIs de moderadores - Banderas y tiempo promedio de resolución de banderas

Explicación de la consulta SQL

Esta consulta evalúa el rendimiento de los moderadores calculando el número de banderas manejadas y el tiempo promedio de resolución (en minutos) para cada moderador.

-- [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 -- tiempo hasta la resolución en minutos
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Tipos de bandera
      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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones marcadas.
:end_date: La fecha de fin para filtrar las publicaciones marcadas.

Explicación de las CTE

period_actions: Filtra las publicaciones marcadas dentro del rango de fechas especificado y calcula el tiempo hasta la resolución para cada bandera.
moderator_actions: Identifica las banderas resueltas por los moderadores y calcula el tiempo hasta la resolución para cada bandera.
moderator_stats: Agrupa las banderas por moderador y calcula el número total de banderas manejadas y el tiempo promedio de resolución.

Explicación de los resultados

El resultado final es una lista clasificada de moderadores con sus recuentos de banderas manejadas y tiempos promedio de resolución.

Ejemplo de resultados

Nombre de usuario del moderador	Banderas manejadas	Tiempo promedio de resolución (minutos)
mod1	50	15.00
mod2	30	20.00

Todos los datos de banderas

Explicación de la consulta SQL

Esta consulta proporciona un conjunto de datos completo de todos los datos de usuario, publicación y tema marcados dentro de un rango de fechas especificado. Combina datos de múltiples tablas para incluir detalles como el tipo de bandera, el elemento marcado, la razón de la bandera, la fuente de la bandera, la decisión de resolución y los mensajes relacionados.

-- [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 -- Incluir solo banderas que fueron aceptadas y se tomó una acción
),
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, -- Elimina solo URLs
    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 -- Diferencia de tiempo en minutos
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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones marcadas.
:end_date: La fecha de fin para filtrar las publicaciones marcadas.

Explicación de las CTE

flag_data: Recupera información detallada sobre las publicaciones marcadas, incluido el ID de la bandera, el elemento marcado, el tipo de bandera, la razón de la bandera, la fuente de la bandera y los detalles de la revisión.
review_decisions: Asigna los códigos de estado de revisión a nombres de decisión legibles por humanos (por ejemplo, pendiente, aceptada, rechazada, ignorada).
flag_types: Asigna los IDs de tipo de acción de publicación a nombres de tipo de bandera legibles por humanos (por ejemplo, fuera de tema, inapropiado, spam).

Explicación de los resultados

ID de bandera: El identificador único para la bandera.
Elemento marcado: El ID de la publicación marcada.
Nombre de usuario que marcó: El nombre de usuario del usuario que marcó la publicación.
Fecha de marcado: La fecha en que se creó la bandera.
Tipo de bandera: El tipo numérico de la bandera.
Nombre del tipo de bandera: El nombre legible por humanos del tipo de bandera (por ejemplo, fuera de tema, spam).
Revisable por moderador: Indica si la bandera fue levantada por un usuario o por el sistema.
Razón de la bandera: La razón proporcionada para la bandera.
Texto del elemento marcado: El contenido de la publicación marcada.
ID del mensaje relacionado: El ID de cualquier mensaje relacionado (si aplica).
Texto del mensaje relacionado: El contenido del mensaje relacionado, con las URLs eliminadas para mayor claridad.
Nombre de usuario que revisó: El nombre de usuario del moderador que revisó la bandera.
Decisión de revisión: La decisión tomada por el revisor (por ejemplo, aceptada, rechazada, ignorada, eliminada).
Acción tomada: La acción tomada como resultado de la bandera, como silenciar o suspender al usuario, eliminar u ocultar la publicación, o ninguna acción.
Tiempo de revisión (minutos): El tiempo tomado para revisar la bandera, calculado como la diferencia entre el tiempo de creación de la bandera y el tiempo de revisión, en minutos.

Ejemplo de resultados (anonimizados)

ID de bandera	Elemento marcado	Nombre de usuario que marcó	Fecha de marcado	Tipo de bandera	Nombre del tipo de bandera	Revisable por moderador	Razón de la bandera	Texto del elemento marcado	ID del mensaje relacionado	Texto del mensaje relacionado	Nombre de usuario que revisó	Decisión de revisión	Acción tomada	Tiempo de revisión (minutos)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Contenido de spam	“Compra ahora en spam.com”	98765	“¡Mira esto!”	mod456	Aceptada	Publicación eliminada	15.25
12346	67891	user124	2025-04-02 14:30	4	Inapropiado	false	Ofensivo	“¡Esto es inapropiado!”	NULL	NULL	mod457	Rechazada	Ninguna acción tomada	30.50

Informes de banderas enviadas

Este informe proporciona una visión general de las banderas enviadas dentro del rango de fechas especificado. Categoriza las banderas por su tipo (por ejemplo, Spam, Inapropiado) y distingue entre banderas reportadas por usuarios y banderas generadas por el sistema. El informe incluye el número total de banderas para cada tipo, lo que ayuda a identificar los problemas más comunes marcados en la plataforma.

-- [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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar las publicaciones reportadas.
:end_date: La fecha de fin para filtrar las publicaciones reportadas.

Explicación de las CTEs

flag_data:
Esta CTE recupera información detallada sobre las publicaciones reportadas, incluyendo:

El ID único del reporte (flag_id), el ID de la publicación reportada (post_id) y el tema al que pertenece (topic_id).
Información sobre el reporte en sí, como el usuario que lo realizó (flagged_by_username), la fecha del reporte (flagged_date), el tipo de reporte (flag_type) y la razón del reporte (flag_reason).
Detalles sobre el proceso de revisión, incluyendo al moderador que revisó el reporte (reviewed_by_username), la decisión de revisión (review_status) y la hora de la revisión (reviewed_at).

flag_types:
Esta CTE asigna los IDs numéricos de tipos de acción de publicación a nombres de tipos de reporte legibles por humanos:

3: Fuera de tema
4: Inapropiado
6: Notificar al usuario
7: Notificar a moderadores
8: Spam
10: Ilegal
NULL: Otra cosa

Explicación de los resultados

La consulta final agrega los datos de reportes por tipo de reporte y proporciona las siguientes métricas:

Tipo: El nombre legible por humanos del tipo de reporte (por ejemplo, Fuera de tema, Spam).
Reportado: El recuento de reportes enviados por usuarios (excluyendo los generados por el sistema).
Automatizado: El recuento de reportes generados por el sistema o bots (por ejemplo, spam_scanner_bot, system).
Total: El número total de reportes para cada tipo.

Los resultados se ordenan por el número total de reportes en orden descendente.

Ejemplo de resultados

Tipo	Reportado	Automatizado	Total
Spam	120	80	200
Inapropiado	90	10	100
Fuera de tema	60	5	65
Notificar_moderadores	30	0	30
Ilegal	10	2	12
Otra cosa	5	0	5

Suspensiones y silenciamientos:

Este informe enumera a los usuarios que fueron suspendidos o silenciados dentro del rango de fechas especificado. Incluye detalles como las fechas de suspensión o silenciamiento, la duración de la acción y las fechas de creación de la cuenta y última actividad del usuario. Este informe es útil para monitorear las acciones de moderación e identificar patrones en el comportamiento de los usuarios que llevan a suspensiones o silenciamientos.

-- [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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar usuarios suspendidos o silenciados.
:end_date: La fecha de fin para filtrar usuarios suspendidos o silenciados.

Explicación de los resultados

Esta consulta recupera una lista de usuarios que fueron suspendidos o silenciados dentro del rango de fechas especificado. Las columnas clave incluyen:

ID de usuario: El identificador único del usuario.
Nombre de usuario: El nombre de usuario del usuario.
Nombre: El nombre completo del usuario (si está disponible).
Suspendido en: La fecha en que el usuario fue suspendido.
Suspendido hasta: La fecha hasta la cual el usuario está suspendido.
Silenciado hasta: La fecha hasta la cual el usuario está silenciado.
Cuenta creada en: La fecha en que se creó la cuenta del usuario.
Visto por última vez: La última vez que el usuario estuvo activo en la plataforma.
Nivel de reporte: El nivel de reporte actual del usuario.
Admin: Si el usuario es administrador (verdadero/falso).
Moderador: Si el usuario es moderador (verdadero/falso).

Los resultados se ordenan por la fecha de suspensión (suspended_at) y la fecha de silenciamiento (silenced_till) en orden descendente, con los valores nulos apareciendo al final.

Ejemplo de resultados

ID de usuario	Nombre de usuario	Nombre	Suspendido en	Suspendido hasta	Silenciado hasta	Cuenta creada en	Visto por última vez	Nivel de reporte	Admin	Moderador
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

Acciones de reporte acordadas

Este informe se centra en los reportes que fueron acordados por los moderadores y resultaron en acciones. Categoriza los reportes por tipo y proporciona métricas como el número total de reportes, el tiempo mediano tomado para actuar sobre ellos y los resultados (por ejemplo, usuarios silenciados, publicaciones eliminadas).

-- [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 -- Only include flags that were agreed with and action was taken
),
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 -- Include mapped flag types
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Include specific flag types for NULL results
        )
    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 -- Include mapped flag types
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Include specific flag types for NULL results
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Parámetros utilizados

:start_date: La fecha de inicio para filtrar reportes acordados.
:end_date: La fecha de fin para filtrar reportes acordados.

Explicación de las CTEs

flag_data:
Esta CTE recupera información detallada sobre los reportes que fueron acordados y tuvieron acciones. Incluye:

El ID único del reporte (flag_id), el ID de la publicación reportada (post_id) y el tema al que pertenece (topic_id).
Información sobre el reporte en sí, como el usuario que lo realizó (flagged_by_username), la fecha del reporte (flagged_date), el tipo de reporte (flag_type) y la razón del reporte (flag_reason).
Detalles sobre el proceso de revisión, incluyendo al moderador que revisó el reporte (reviewed_by_username), la decisión de revisión (review_status) y la hora de la revisión (reviewed_at).
Información adicional sobre la publicación reportada, como si fue eliminada, oculta o si el autor fue silenciado o suspendido.

flag_types:
Esta CTE asigna los IDs numéricos de tipos de acción de publicación a nombres de tipos de reporte legibles por humanos:

3: Fuera de tema
4: Inapropiado
6: Notificar al usuario
7: Notificar a moderadores
8: Spam
10: Ilegal

median_time_to_act:
Esta CTE calcula el tiempo mediano (en minutos) tomado para actuar sobre cada tipo de reporte. El tiempo se calcula como la diferencia entre la hora de creación del reporte (flagged_date) y la hora de revisión (reviewed_at).

Explicación de los resultados

La consulta final agrega los datos de reportes acordados por tipo de reporte y proporciona las siguientes métricas:

Tipo: El nombre legible por humanos del tipo de reporte (por ejemplo, Fuera de tema, Spam).
Reportado: El recuento de reportes enviados por usuarios (excluyendo los generados por el sistema).
Automatizado: El recuento de reportes generados por el sistema o bots (por ejemplo, spam_scanner_bot, system).
Total: El número total de reportes para cada tipo.
Tiempo mediano para actuar (minutos): El tiempo mediano tomado para actuar sobre reportes de este tipo, en minutos.
Usuario silenciado: El recuento de reportes que resultaron en que el usuario fuera silenciado.
Usuario eliminado: El recuento de reportes que resultaron en que el usuario fuera suspendido.
Publicación eliminada: El recuento de reportes que resultaron en que la publicación fuera eliminada.
Publicación oculta: El recuento de reportes que resultaron en que la publicación fuera oculta.

Los resultados se ordenan por el número total de reportes en orden descendente.

Ejemplo de resultados

Tipo	Reportado	Automatizado	Total	Tiempo mediano para actuar (minutos)	Usuario silenciado	Usuario eliminado	Publicación eliminada	Publicación oculta
Spam	100	50	150	30	20	10	50	30
Inapropiado	80	5	85	45	15	5	30	20
Fuera de tema	40	2	42	25	5	0	10	15
Notificar_moderadores	20	0	20	60	0	0	5	10
Ilegal	5	1	6	120	1	1	3	2

Acciones de moderación realizadas

Este informe proporciona un resumen de las acciones de moderación realizadas dentro del rango de fechas especificado. Agrega varios tipos de acciones acordadas, incluyendo contenido reportado por usuarios o automatización, publicaciones eliminadas u ocultas, advertencias emitidas, cuentas eliminadas o suspendidas y usuarios silenciados por períodos prolongados. Cada categoría se presenta con el número total de casos, ofreciendo una visión general de alta nivel de la actividad de moderación.

-- [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 -- Only include flags that were agreed with and action was taken
),
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 -- Include mapped flag types
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Include specific flag types for NULL results
),
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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar acciones de moderación.
:end_date: La fecha de fin para filtrar acciones de moderación.

Explicación de los resultados

La consulta agrega las acciones de moderación en categorías y proporciona el número total de casos para cada categoría. Las categorías incluyen:

Contenido reportado por usuarios: El número de publicaciones reportadas por usuarios regulares (excluyendo los generados por el sistema).
Contenido reportado por automatización: El número de publicaciones reportadas por sistemas automatizados o bots (por ejemplo, spam_scanner_bot, system).
Publicaciones eliminadas por violar términos: El número de publicaciones que fueron eliminadas debido a violaciones de las directrices comunitarias o términos de servicio.
Publicaciones ocultas: El número de publicaciones que fueron ocultas (pero no eliminadas) por varias razones.
Advertencias emitidas: El número de advertencias emitidas a usuarios por comportamiento o contenido inapropiado.
Cuentas eliminadas: El número de cuentas de usuario eliminadas debido a violaciones, como ser reportadas como spammers o rechazadas en colas de revisión.
Cuentas suspendidas: El número de cuentas de usuario suspendidas por un período específico debido a violaciones.
Usuarios silenciados por 10+ años: El número de usuarios silenciados indefinidamente o por períodos prolongados (10+ años).

Ejemplo de resultados

Categoría	Número de casos
Contenido reportado por usuarios	150
Contenido reportado por automatización	100
Publicaciones eliminadas por violar términos	50
Publicaciones ocultas	30
Advertencias emitidas	20
Cuentas eliminadas	10
Cuentas suspendidas	15
Usuarios silenciados por 10+ años	5

Acciones de moderación individuales realizadas

Este informe proporciona un registro detallado de las acciones de moderación individuales acordadas realizadas dentro del rango de fechas especificado. Incluye información sobre el usuario que realizó la acción, el usuario objetivo, la fecha de la acción, la categoría de la acción (por ejemplo, contenido reportado, publicaciones eliminadas, advertencias emitidas) y el contexto o razón de la acción. Este informe es útil para auditar decisiones específicas de moderación y comprender el contexto detrás de cada acción.

-- [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 -- Only include flags that were agreed with and action was taken
),
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

Parámetros utilizados

:start_date: La fecha de inicio para filtrar acciones de moderación individuales.
:end_date: La fecha de fin para filtrar acciones de moderación individuales.

Explicación de los resultados

La consulta proporciona un registro detallado de acciones de moderación individuales, incluyendo:

Usuario que actuó: El nombre de usuario del moderador, sistema o usuario que realizó la acción.
Usuario objetivo: El nombre de usuario o ID del usuario que fue el sujeto de la acción (por ejemplo, el autor de una publicación reportada o el destinatario de una advertencia).
Fecha de la acción: La fecha y hora en que ocurrió la acción.
Categoría: El tipo de acción de moderación, como:

Contenido reportado por usuarios
Contenido reportado por automatización
Publicaciones eliminadas por violar términos
Publicaciones ocultas
Advertencias emitidas
Cuentas eliminadas
Cuentas suspendidas
Usuarios silenciados por 10+ años

Contexto: Información adicional o contenido relacionado con la acción, como el texto de una publicación reportada o la razón de una suspensión.

Ejemplo de resultados

Usuario que actuó	Usuario objetivo	Fecha de la acción	Categoría	Contexto
user123	user456	2024-02-01 10:00	Contenido reportado por usuarios	“Esta publicación contiene contenido de spam.”
spam_scanner	user789	2024-02-02 12:00	Contenido reportado por automatización	“Detectado como spam por el sistema.”
mod001	user456	2024-02-03 14:00	Publicaciones eliminadas por violar términos	“Contenido de ejemplo de publicación
mod002	user123	2024-02-04 16:00	Publicaciones ocultas	“Publicación considerada inapropiada.”
admin001	user789	2024-02-05 18:00	Advertencias emitidas	NULL
admin002	user456	2024-02-06 20:00	Cuentas eliminadas	“Cuenta eliminada a través de la cola de revisión.”
mod003	user123	2024-02-07 22:00	Cuentas suspendidas	“Usuario suspendido por spam repetido.”
admin003	user789	2024-02-08 08:00	Usuarios silenciados por 10+ años	“Usuario silenciado por violaciones extremas.”

Tema		Respuestas	Vistas
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	439	22 Diciembre 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	372	6 Febrero 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	844	30 Octubre 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	173	31 Mayo 2025
Show what action was taken and by who for Flags Feature	2	926	12 Abril 2014

Análisis de informes de moderación y actividad de marcado

Porcentaje de resolución de banderas de publicaciones por tipo

Explicación de la consulta SQL

Parámetros utilizados

Explicación de las CTE

Explicación de los resultados

Ejemplo de resultados

Porcentajes de resolución de elementos revisables

Explicación de la consulta SQL

Parámetros utilizados

Explicación de las CTE

Explicación de los resultados

Ejemplo de resultados

Resoluciones de banderas de moderadores

Explicación de la consulta SQL

Parámetros utilizados

Explicación de las CTE

Explicación de los resultados

Ejemplo de resultados

Quién está marcando publicaciones

Explicación de la consulta SQL

Parámetros utilizados

Explicación de los resultados

Ejemplo de resultados

Notas de usuario

Explicación de la consulta SQL

Parámetros utilizados

Explicación de los resultados

Ejemplo de resultados

KPIs de moderadores - Banderas y tiempo promedio de resolución de banderas

Explicación de la consulta SQL

Parámetros utilizados

Explicación de las CTE

Explicación de los resultados

Ejemplo de resultados

Todos los datos de banderas

Explicación de la consulta SQL

Parámetros utilizados

Explicación de las CTE

Explicación de los resultados

Ejemplo de resultados (anonimizados)

Ejemplo de resultados (anonimizados)

Informes de banderas enviadas

Parámetros utilizados

Explicación de las CTEs

Explicación de los resultados

Ejemplo de resultados

Suspensiones y silenciamientos:

Parámetros utilizados

Explicación de los resultados

Ejemplo de resultados

Acciones de reporte acordadas

Parámetros utilizados

Explicación de las CTEs

Explicación de los resultados

Ejemplo de resultados

Acciones de moderación realizadas

Parámetros utilizados

Explicación de los resultados

Ejemplo de resultados

Acciones de moderación individuales realizadas

Parámetros utilizados

Explicación de los resultados

Ejemplo de resultados

Temas relacionados