Analisando relatórios de moderação e atividade de sinalização

SaraDev · 13 Fevereiro , 2025 23:08

Manter uma comunidade saudável e inclusiva exige uma moderação eficaz, que pode incluir a revisão de posts sinalizados, a análise do desempenho dos moderadores e o gerenciamento de conteúdo de usuários.

Este guia contém uma variedade de relatórios SQL para o Discourse, projetados para ajudar na análise de atividades relacionadas à moderação.

Neste tópico, você encontrará consultas detalhadas do Data Explorer para:

Estatísticas de resolução de sinalizações.
Percentuais de resolução de itens passíveis de revisão.
Métricas de desempenho específicas de moderadores.
Informações sobre a atividade de sinalização de usuários.
Dados abrangentes de todas as ações de sinalização de usuários, posts e tópicos.

Percentual de Resolução de Sinalizações de Posts por Tipo

Explicação da Consulta SQL

Esta consulta calcula o percentual de resoluções (concordadas, discordadas, adiadas, excluídas) para posts sinalizados, agrupados por tipo de sinalização.

-- [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 -- tempo para resolução em 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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.

Explicação das CTEs

period_actions: Filtra posts sinalizados dentro do intervalo de datas especificado e calcula o tempo para resolução de cada sinalização.
flag_types: Mapeia IDs de tipos de sinalização para nomes legíveis por humanos (ex.: fora do tópico, inadequado, spam).
flag_resolutions: Agrupa sinalizações por tipo e resolução (concordada, discordada, adiada, excluída) e conta as ocorrências de cada resolução.
flag_totals: Calcula o número total de sinalizações para cada tipo de sinalização.
resolution_percentages: Combina contagens de resolução e totais de sinalizações para calcular o percentual de cada tipo de resolução para cada tipo de sinalização.
pivoted_data: Transpõe os dados para exibir percentuais e contagens de resolução em colunas separadas para cada tipo de resolução.

Explicação dos Resultados

O resultado final é uma tabela mostrando:

Tipo de sinalização (ex.: fora do tópico, spam).
Percentuais e contagens para cada tipo de resolução (concordada, discordada, adiada, excluída).
Total de sinalizações para cada tipo de sinalização.

Exemplo de Resultados

Tipo de Sinalização	% Concordada	Contagem Concordada	% Discordada	Contagem Discordada	% Adiada	Contagem Adiada	% Excluída	Contagem Excluída	Total de Sinalizações
fora_do_tópico	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

Percentuais de Resolução de Itens Passíveis de Revisão

Explicação da Consulta SQL

Esta consulta analisa os status de resolução de itens passíveis de revisão (ex.: posts sinalizados) dentro de um intervalo de datas dado. Ela calcula o percentual e a contagem de cada status de resolução para cada tipo de sinalização.

-- [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,
    -- Percentuais
    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,
    -- Contagens
    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: A data inicial para filtrar itens passíveis de revisão.
:end_date: A data final para filtrar itens passíveis de revisão.

Explicação das CTEs

flag_data: Agrupa itens passíveis de revisão por tipo de sinalização e status de resolução, contando as ocorrências de cada combinação.
flag_totals: Calcula o número total de sinalizações para cada tipo de sinalização.
flag_percentages: Combina contagens de sinalizações e totais para calcular o percentual de cada status de resolução para cada tipo de sinalização.

Explicação dos Resultados

O resultado final é uma tabela mostrando:

Tipo de sinalização.
Percentuais e contagens para cada status de resolução (pendente, aprovado, rejeitado, ignorado, excluído).

Exemplo de Resultados

Tipo de Sinalização	% Pendente	Contagem Pendente	% Aprovado	Contagem Aprovada	% Rejeitado	Contagem Rejeitada	% Ignorado	Contagem Ignorada	% Excluído	Contagem Excluída
fora_do_tópico	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

Resoluções de Sinalizações por Moderadores

Explicação da Consulta SQL

Esta consulta fornece insights sobre a atividade dos moderadores, mostrando quais moderadores resolveram posts sinalizados, os tipos de sinalizações que trataram e as resoluções que aplicaram. Ela calcula percentuais e contagens para cada tipo de resolução.

-- [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 -- tempo para resolução em 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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.
:only_staff: Um parâmetro booleano para filtrar resultados incluindo apenas membros da equipe.

Explicação das CTEs

period_actions: Filtra posts sinalizados dentro do intervalo de datas especificado e calcula o tempo para resolução de cada sinalização.
flag_types: Mapeia IDs de tipos de sinalização para nomes legíveis por humanos.
flag_resolutions: Agrupa sinalizações por usuário, tipo de sinalização e resolução, contando as ocorrências de cada combinação.
flag_totals: Calcula o número total de sinalizações para cada usuário e tipo de sinalização.
resolution_percentages: Combina contagens de resolução e totais para calcular percentuais para cada tipo de resolução.
pivoted_data: Transpõe os dados para exibir percentuais e contagens de resolução em colunas separadas para cada tipo de resolução.

Explicação dos Resultados

O resultado final é uma tabela mostrando:

Nome de usuário do moderador.
Percentuais e contagens para cada tipo de resolução (concordada, discordada, adiada, excluída).
Total de sinalizações tratadas por cada moderador.

Exemplo de Resultados

Moderador	Tipo de Sinalização	% Concordada	Contagem Concordada	% Discordada	Contagem Discordada	% Adiada	Contagem Adiada	% Excluída	Contagem Excluída	Total de Sinalizações
mod1	fora_do_tópico	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

Quem Está Sinalizando Posts

Explicação da Consulta SQL

Esta consulta identifica os usuários que sinalizaram posts dentro de um intervalo de datas especificado e calcula o número total de sinalizações enviadas por cada usuário.

-- [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 sinalização
  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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.
:only_staff: Um parâmetro booleano para filtrar resultados incluindo apenas membros da equipe.

Explicação dos Resultados

O resultado final é uma lista classificada de usuários com suas contagens de sinalizações.

Exemplo de Resultados

ID do Usuário	Nome de Usuário	Contagem de Sinalizações
1	user1	50
2	user2	30
3	user3	20

Notas de Usuário

Explicação da Consulta SQL

Esta consulta recupera notas de usuário armazenadas na tabela plugin_store_rows. Ela extrai detalhes como o ID do usuário, data de criação, conteúdo da nota e o ID do criador.

-- [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: A data inicial para filtrar notas de usuário.
:end_date: A data final para filtrar notas de usuário.

Explicação dos Resultados

O resultado final é uma lista detalhada de notas de usuário com metadados relevantes.

Exemplo de Resultados

ID do Usuário	Criado Em	Nota do Usuário	ID do Usuário Criador
1	2025-01-01	Este usuário é útil.	2
2	2025-02-01	Este usuário está associado a duas outras contas de usuário.	3

KPIs de Moderadores - Sinalizações e Tempo Médio de Resolução

Explicação da Consulta SQL

Esta consulta avalia o desempenho dos moderadores calculando o número de sinalizações tratadas e o tempo médio de resolução (em 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 -- tempo para resolução em minutos
    FROM post_actions pa
    WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Tipos de sinalização
      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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.

Explicação das CTEs

period_actions: Filtra posts sinalizados dentro do intervalo de datas especificado e calcula o tempo para resolução de cada sinalização.
moderator_actions: Identifica sinalizações resolvidas por moderadores e calcula o tempo para resolução de cada sinalização.
moderator_stats: Agrupa sinalizações por moderador e calcula o número total de sinalizações tratadas e o tempo médio de resolução.

Explicação dos Resultados

O resultado final é uma lista classificada de moderadores com suas contagens de sinalizações tratadas e tempos médios de resolução.

Exemplo de Resultados

Nome de Usuário do Moderador	Sinalizações Tratadas	Tempo Médio de Resolução (Minutos)
mod1	50	15.00
mod2	30	20.00

Todos os Dados de Sinalização

Explicação da Consulta SQL

Esta consulta fornece um conjunto de dados abrangente de todos os dados de sinalização de usuários, posts e tópicos dentro de um intervalo de datas especificado. Ela combina dados de várias tabelas para incluir detalhes como o tipo de sinalização, item sinalizado, motivo da sinalização, fonte da sinalização, decisão de resolução e mensagens relacionadas.

-- [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 apenas sinalizações que foram concordadas e ação foi tomada
),
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, -- Remove apenas 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 -- Diferença de tempo em 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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.

Explicação das CTEs

flag_data: Recupera informações detalhadas sobre posts sinalizados, incluindo o ID da sinalização, item sinalizado, tipo de sinalização, motivo da sinalização, fonte da sinalização e detalhes da revisão.
review_decisions: Mapeia códigos de status de revisão para nomes de decisão legíveis por humanos (ex.: pendente, concordada, discordada, ignorada).
flag_types: Mapeia IDs de tipos de ação de post para nomes de tipos de sinalização legíveis por humanos (ex.: fora do tópico, inadequado, spam).

Explicação dos Resultados

ID da Sinalização: O identificador único da sinalização.
Item Sinalizado: O ID do post sinalizado.
Nome de Usuário que Sinalizou: O nome de usuário do usuário que sinalizou o post.
Data da Sinalização: A data em que a sinalização foi criada.
Tipo de Sinalização: O tipo numérico da sinalização.
Nome do Tipo de Sinalização: O nome legível por humano do tipo de sinalização (ex.: fora do tópico, spam).
Revisável por Moderador: Indica se a sinalização foi levantada por um usuário ou pelo sistema.
Motivo da Sinalização: O motivo fornecido para a sinalização.
Texto do Item Sinalizado: O conteúdo do post sinalizado.
ID da Mensagem Relacionada: O ID de qualquer mensagem relacionada (se aplicável).
Texto da Mensagem Relacionada: O conteúdo da mensagem relacionada, com URLs removidas para clareza.
Nome de Usuário que Revisou: O nome de usuário do moderador que revisou a sinalização.
Decisão de Revisão: A decisão tomada pelo revisor (ex.: concordada, discordada, ignorada, excluída).
Ação Tomada: A ação tomada como resultado da sinalização, como silenciar ou suspender o usuário, excluir ou ocultar o post, ou nenhuma ação.
Tempo de Revisão (Minutos): O tempo gasto para revisar a sinalização, calculado como a diferença entre o tempo de criação da sinalização e o tempo de revisão, em minutos.

Exemplo de Resultados (Anonimizado)

ID da Sinalização	Item Sinalizado	Nome de Usuário que Sinalizou	Data da Sinalização	Tipo de Sinalização	Nome do Tipo de Sinalização	Revisável por Moderador	Motivo da Sinalização	Texto do Item Sinalizado	ID da Mensagem Relacionada	Texto da Mensagem Relacionada	Nome de Usuário que Revisou	Decisão de Revisão	Ação Tomada	Tempo de Revisão (Minutos)
12345	67890	user123	2025-04-01 12:00	8	Spam	true	Conteúdo de spam	“Compre agora em spam.com”	98765	“Confira isso!”	mod456	Concordada	Post excluído	15.25
12346	67891	user124	2025-04-02 14:30	4	Inadequado	false	Ofensivo	“Isso é inadequado!”	NULL	NULL	mod457	Discordada	Nenhuma ação tomada	30.50

Relatórios de Sinalizações Enviadas

Este relatório fornece uma visão geral das sinalizações enviadas dentro do intervalo de datas especificado. Ele categoriza as sinalizações por seu tipo (ex.: Spam, Inadequado) e distingue entre sinalizações relatadas por usuários e sinalizações geradas pelo sistema. O relatório inclui o número total de sinalizações para cada tipo, ajudando a identificar os problemas mais comuns sinalizados na 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, 'Fora do tópico' AS flag_type_name
    UNION ALL
    SELECT
        4 AS post_action_type_id, 'Inadequado' AS flag_type_name
    UNION ALL
    SELECT
        6 AS post_action_type_id, 'Notificar usuário' AS flag_type_name
    UNION ALL
    SELECT
        7 AS post_action_type_id, 'Notificar moderadores' 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, 'Ilegal' AS flag_type_name
    UNION ALL
    SELECT
        NULL AS post_action_type_id, 'Outro' AS flag_type_name
)
SELECT
    ft.flag_type_name AS Tipo,
    COUNT(CASE WHEN fd.flagged_by_username NOT IN ('spam_scanner_bot', 'system') THEN 1 END) AS Relatado,
    COUNT(CASE WHEN fd.flagged_by_username IN ('spam_scanner_bot', 'system') THEN 1 END) AS Automatizado,
    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: A data inicial para filtrar posts sinalizados.
:end_date: A data final para filtrar posts sinalizados.

Explicação dos CTEs

flag_data:
Este CTE recupera informações detalhadas sobre posts sinalizados, incluindo:

O ID único da sinalização (flag_id), o ID do post sinalizado (post_id) e o tópico ao qual pertence (topic_id).
Informações sobre a própria sinalização, como o usuário que a fez (flagged_by_username), a data da sinalização (flagged_date), o tipo de sinalização (flag_type) e o motivo da sinalização (flag_reason).
Detalhes sobre o processo de revisão, incluindo o moderador que revisou a sinalização (reviewed_by_username), a decisão de revisão (review_status) e o horário da revisão (reviewed_at).

flag_types:
Este CTE mapeia IDs numéricos de tipos de ação de post para nomes legíveis de tipos de sinalização:

3: Fora do assunto
4: Inadequado
6: Notificar usuário
7: Notificar moderadores
8: Spam
10: Ilegal
NULL: Outra coisa

Explicação dos Resultados

A consulta final agrega os dados de sinalização por tipo de sinalização e fornece as seguintes métricas:

Tipo: O nome legível do tipo de sinalização (por exemplo, Fora do assunto, Spam).
Reportado: A contagem de sinalizações enviadas por usuários (excluindo sinalizações geradas pelo sistema).
Automatizado: A contagem de sinalizações geradas pelo sistema ou bots (por exemplo, spam_scanner_bot, system).
Total: O número total de sinalizações para cada tipo.

Os resultados são ordenados pelo número total de sinalizações em ordem decrescente.

Exemplo de Resultados

Tipo	Reportado	Automatizado	Total
Spam	120	80	200
Inadequado	90	10	100
Fora do assunto	60	5	65
Notificar_moderadores	30	0	30
Ilegal	10	2	12
Outra coisa	5	0	5

Banimentos e Suspensões:

Este relatório lista usuários que foram suspensos ou silenciados dentro do intervalo de datas especificado. Inclui detalhes como as datas de suspensão ou silenciamento, a duração da ação e as datas de criação da conta e última atividade do usuário. Este relatório é útil para monitorar ações de moderação e identificar padrões de comportamento de usuários que levam a banimentos ou silenciamentos.

-- [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: A data inicial para filtrar usuários suspensos ou silenciados.
:end_date: A data final para filtrar usuários suspensos ou silenciados.

Explicação dos Resultados

Esta consulta recupera uma lista de usuários que foram suspensos ou silenciados dentro do intervalo de datas especificado. As colunas principais incluem:

ID do Usuário: O identificador único do usuário.
Nome de Usuário: O nome de usuário do usuário.
Nome: O nome completo do usuário (se disponível).
Suspenso Em: A data em que o usuário foi suspenso.
Suspenso Até: A data até a qual o usuário está suspenso.
Silenciado Até: A data até a qual o usuário está silenciado.
Conta Criada Em: A data em que a conta do usuário foi criada.
Visto pela Última Vez Em: A última vez que o usuário esteve ativo na plataforma.
Nível de Sinalização: O nível atual de sinalização do usuário.
Admin: Se o usuário é administrador (verdadeiro/falso).
Moderador: Se o usuário é moderador (verdadeiro/falso).

Os resultados são ordenados pela data de suspensão (suspended_at) e data de silenciamento (silenced_till) em ordem decrescente, com valores nulos aparecendo por último.

Exemplo de Resultados

ID do Usuário	Nome de Usuário	Nome	Suspenso Em	Suspenso Até	Silenciado Até	Conta Criada Em	Visto pela Última Vez Em	Nível de Sinalização	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

Ações de Sinalização Aceites

Este relatório foca em sinalizações que foram aceitas pelos moderadores e resultaram em ações tomadas. Categoriza as sinalizações por tipo e fornece métricas como o número total de sinalizações, o tempo mediano gasto para agir sobre elas e os resultados (por exemplo, usuários silenciados, posts excluídos).

-- [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 -- Incluir apenas sinalizações que foram aceitas e ação foi tomada
),
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 -- Incluir tipos de sinalização mapeados
            OR fd.flag_type IN (
                'ReviewableAkismetPost',
                'ReviewableUser',
                'ReviewableFlaggedPost',
                'ReviewableChatMessage',
                'ReviewablePost',
                'ReviewableQueuedPost'
            ) -- Incluir tipos de sinalização específicos para resultados 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 -- Incluir tipos de sinalização mapeados
    OR fd.flag_type IN (
        'ReviewableAkismetPost',
        'ReviewableUser',
        'ReviewableFlaggedPost',
        'ReviewableChatMessage',
        'ReviewablePost',
        'ReviewableQueuedPost'
    ) -- Incluir tipos de sinalização específicos para resultados NULL
GROUP BY
    COALESCE(ft.flag_type_name, fd.flag_type), mta.median_time_minutes
ORDER BY
    Total DESC

Parâmetros Utilizados

:start_date: A data inicial para filtrar sinalizações aceitas.
:end_date: A data final para filtrar sinalizações aceitas.

Explicação dos CTEs

flag_data:
Este CTE recupera informações detalhadas sobre sinalizações que foram aceitas e tiveram ações tomadas. Inclui:

O ID único da sinalização (flag_id), o ID do post sinalizado (post_id) e o tópico ao qual pertence (topic_id).
Informações sobre a própria sinalização, como o usuário que a fez (flagged_by_username), a data da sinalização (flagged_date), o tipo de sinalização (flag_type) e o motivo da sinalização (flag_reason).
Detalhes sobre o processo de revisão, incluindo o moderador que revisou a sinalização (reviewed_by_username), a decisão de revisão (review_status) e o horário da revisão (reviewed_at).
Informações adicionais sobre o post sinalizado, como se foi excluído, ocultado ou se o autor foi silenciado ou suspenso.

flag_types:
Este CTE mapeia IDs numéricos de tipos de ação de post para nomes legíveis de tipos de sinalização:

3: Fora do assunto
4: Inadequado
6: Notificar usuário
7: Notificar moderadores
8: Spam
10: Ilegal

median_time_to_act:
Este CTE calcula o tempo mediano (em minutos) gasto para agir sobre cada tipo de sinalização. O tempo é calculado como a diferença entre o horário de criação da sinalização (flagged_date) e o horário de revisão (reviewed_at).

Explicação dos Resultados

A consulta final agrega os dados de sinalização aceitos por tipo de sinalização e fornece as seguintes métricas:

Tipo: O nome legível do tipo de sinalização (por exemplo, Fora do assunto, Spam).
Reportado: A contagem de sinalizações enviadas por usuários (excluindo sinalizações geradas pelo sistema).
Automatizado: A contagem de sinalizações geradas pelo sistema ou bots (por exemplo, spam_scanner_bot, system).
Total: O número total de sinalizações para cada tipo.
Tempo Mediano para Agir (Minutos): O tempo mediano gasto para agir sobre sinalizações deste tipo, em minutos.
Usuário Silenciado: A contagem de sinalizações que resultaram no silenciamento do usuário.
Usuário Excluído: A contagem de sinalizações que resultaram na suspensão do usuário.
Post Excluído: A contagem de sinalizações que resultaram na exclusão do post.
Post Ocultado: A contagem de sinalizações que resultaram na ocultação do post.

Os resultados são ordenados pelo número total de sinalizações em ordem decrescente.

Exemplo de Resultados

Tipo	Reportado	Automatizado	Total	Tempo Mediano para Agir (Minutos)	Usuário Silenciado	Usuário Excluído	Post Excluído	Post Ocultado
Spam	100	50	150	30	20	10	50	30
Inadequado	80	5	85	45	15	5	30	20
Fora do assunto	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

Ações de Moderação Tomadas

Este relatório fornece um resumo das ações de moderação tomadas dentro do intervalo de datas especificado. Agrega vários tipos de ações aceitas, incluindo conteúdo sinalizado por usuários ou automação, posts excluídos ou ocultados, advertências emitidas, contas excluídas ou suspensas e usuários silenciados por longos períodos. Cada categoria é apresentada com o número total de casos, oferecendo uma visão geral de alto nível da atividade de moderação.

-- [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 -- Incluir apenas sinalizações que foram aceitas e ação foi tomada
),
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 -- Incluir tipos de sinalização mapeados
        OR fd.flag_type IN (
            'ReviewableAkismetPost',
            'ReviewableUser',
            'ReviewableFlaggedPost',
            'ReviewableChatMessage',
            'ReviewablePost',
            'ReviewableQueuedPost'
        ) -- Incluir tipos de sinalização específicos para resultados 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

Parâmetros Utilizados

:start_date: A data inicial para filtrar ações de moderação.
:end_date: A data final para filtrar ações de moderação.

Explicação dos Resultados

A consulta agrega ações de moderação em categorias e fornece o número total de casos para cada categoria. As categorias incluem:

Conteúdo sinalizado por usuários: O número de posts sinalizados por usuários regulares (excluindo sinalizações geradas pelo sistema).
Conteúdo sinalizado por automação: O número de posts sinalizados por sistemas automatizados ou bots (por exemplo, spam_scanner_bot, system).
Posts excluídos por violar termos: O número de posts que foram excluídos devido a violações das diretrizes da comunidade ou termos de serviço.
Posts ocultados: O número de posts que foram ocultados (mas não excluídos) por vários motivos.
Advertências Emitidas: O número de advertências emitidas a usuários por comportamento ou conteúdo inadequado.
Contas excluídas: O número de contas de usuário excluídas devido a violações, como serem sinalizadas como spammers ou rejeitadas em filas de revisão.
Contas suspensas: O número de contas de usuário suspensas por um período específico devido a violações.
Usuários silenciados por 10+ anos: O número de usuários silenciados indefinidamente ou por longos períodos (10+ anos).

Exemplo de Resultados

Categoria	Número de Casos
Conteúdo sinalizado por usuários	150
Conteúdo sinalizado por automação	100
Posts excluídos por violar termos	50
Posts ocultados	30
Advertências Emitidas	20
Contas excluídas	10
Contas suspensas	15
Usuários silenciados por 10+ anos	5

Ações Individuais de Moderação Tomadas

Este relatório fornece um registro detalhado das ações individuais de moderação aceitas tomadas dentro do intervalo de datas especificado. Inclui informações sobre o usuário que realizou a ação, o usuário alvo, a data da ação, a categoria da ação (por exemplo, conteúdo sinalizado, posts excluídos, advertências emitidas) e o contexto ou motivo da ação. Este relatório é útil para auditar decisões específicas de moderação e entender o contexto por trás de cada ação.

-- [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 -- Incluir apenas sinalizações que foram aceitas e ação foi tomada
),
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: A data inicial para filtrar ações individuais de moderação.
:end_date: A data final para filtrar ações individuais de moderação.

Explicação dos Resultados

A consulta fornece um registro detalhado de ações individuais de moderação, incluindo:

Usuário Agente: O nome de usuário do moderador, sistema ou usuário que realizou a ação.
Usuário Alvo: O nome de usuário ou ID do usuário que foi o alvo da ação (por exemplo, o autor de um post sinalizado ou o destinatário de uma advertência).
Data da Ação: A data e hora em que a ação ocorreu.
Categoria: O tipo de ação de moderação, como:

Conteúdo sinalizado por usuários
Conteúdo sinalizado por automação
Posts excluídos por violar termos
Posts ocultados
Advertências Emitidas
Contas excluídas
Contas suspensas
Usuários silenciados por 10+ anos

Contexto: Informações adicionais ou conteúdo relacionado à ação, como o texto de um post sinalizado ou o motivo de uma suspensão.

Exemplo de Resultados

Usuário Agente	Usuário Alvo	Data da Ação	Categoria	Contexto
user123	user456	2024-02-01 10:00	Conteúdo sinalizado por usuários	“Este post contém conteúdo de spam.”
spam_scanner	user789	2024-02-02 12:00	Conteúdo sinalizado por automação	“Detectado como spam pelo sistema.”
mod001	user456	2024-02-03 14:00	Posts excluídos por violar termos	“Exemplo de Conteúdo de Post
mod002	user123	2024-02-04 16:00	Posts ocultados	“Post considerado inadequado.”
admin001	user789	2024-02-05 18:00	Advertências Emitidas	NULL
admin002	user456	2024-02-06 20:00	Contas excluídas	“Conta excluída via fila de revisão.”
mod003	user123	2024-02-07 22:00	Contas suspensas	“Usuário suspenso por spam repetido.”
admin003	user789	2024-02-08 08:00	Usuários silenciados por 10+ anos	“Usuário silenciado por violações extremas.”

Tópico		Respostas	Vistas
Dashboard Report - Flags Status Data & reporting sql-query , dashboard-reports , dashboard-sql	0	430	22 Dezembro , 2023
Dashboard Report - User Flagging Ratio Data & reporting sql-query , dashboard-reports , dashboard-sql	0	353	6 Fevereiro , 2024
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	782	30 Outubro , 2024
Statistics on spam flags reported by AI triage automation Data & reporting ai , sql-query , spam	3	144	31 Maio , 2025
Show what action was taken and by who for Flags Feature	2	909	12 Abril , 2014

Analisando relatórios de moderação e atividade de sinalização

Percentual de Resolução de Sinalizações de Posts por Tipo

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação das CTEs

Explicação dos Resultados

Exemplo de Resultados

Percentuais de Resolução de Itens Passíveis de Revisão

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação das CTEs

Explicação dos Resultados

Exemplo de Resultados

Resoluções de Sinalizações por Moderadores

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação das CTEs

Explicação dos Resultados

Exemplo de Resultados

Quem Está Sinalizando Posts

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação dos Resultados

Exemplo de Resultados

Notas de Usuário

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação dos Resultados

Exemplo de Resultados

KPIs de Moderadores - Sinalizações e Tempo Médio de Resolução

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação das CTEs

Explicação dos Resultados

Exemplo de Resultados

Todos os Dados de Sinalização

Explicação da Consulta SQL

Parâmetros Utilizados

Explicação das CTEs

Explicação dos Resultados

Exemplo de Resultados (Anonimizado)

Exemplo de Resultados (Anonimizado)

Relatórios de Sinalizações Enviadas

Parâmetros Utilizados

Explicação dos CTEs

Explicação dos Resultados

Exemplo de Resultados

Banimentos e Suspensões:

Parâmetros Utilizados

Explicação dos Resultados

Exemplo de Resultados

Ações de Sinalização Aceites

Parâmetros Utilizados

Explicação dos CTEs

Explicação dos Resultados

Exemplo de Resultados

Ações de Moderação Tomadas

Parâmetros Utilizados

Explicação dos Resultados

Exemplo de Resultados

Ações Individuais de Moderação Tomadas

Parâmetros Utilizados

Explicação dos Resultados

Exemplo de Resultados

Related topics