Esta é uma versão SQL do Relatório de Status de Bandeiras.
O relatório de Status de Bandeiras foi projetado para fornecer insights sobre o processo de moderação, analisando o tempo necessário para resolver postagens sinalizadas em um período especificado. Ele inclui detalhes sobre o tipo de bandeira levantada, a postagem e os usuários envolvidos, a data de criação da bandeira, o status da resolução, o membro da equipe que lidou com a bandeira e o tempo necessário para chegar a uma resolução.
Este relatório é útil para gerentes de comunidade e moderadores avaliarem a eficiência da equipe de moderação, identificarem quaisquer problemas no processo de resolução e garantirem que os padrões da comunidade sejam mantidos em tempo hábil.
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
WITH period_actions AS (
SELECT pa.id,
pa.post_action_type_id,
pa.created_at,
pa.agreed_at,
pa.disagreed_at,
pa.deferred_at,
pa.agreed_by_id,
pa.disagreed_by_id,
pa.deferred_by_id,
pa.post_id,
pa.user_id,
COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) AS responded_at,
EXTRACT(EPOCH FROM (COALESCE(pa.disagreed_at, pa.agreed_at, pa.deferred_at) - pa.created_at)) / 60 AS time_to_resolution_minutes -- 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
),
poster_data AS (
SELECT pa.id,
p.user_id AS poster_id,
p.topic_id,
p.post_number,
u.username_lower AS poster_username,
u.uploaded_avatar_id AS poster_avatar_id
FROM period_actions pa
JOIN posts p ON p.id = pa.post_id
JOIN users u ON u.id = p.user_id
),
flagger_data AS (
SELECT pa.id,
u.id AS flagger_id,
u.username_lower AS flagger_username,
u.uploaded_avatar_id AS flagger_avatar_id
FROM period_actions pa
JOIN users u ON u.id = pa.user_id
),
staff_data AS (
SELECT pa.id,
u.id AS staff_id,
u.username_lower AS staff_username,
u.uploaded_avatar_id AS staff_avatar_id
FROM period_actions pa
JOIN users u ON u.id = COALESCE(pa.agreed_by_id, pa.disagreed_by_id, pa.deferred_by_id)
),
flag_types AS (
SELECT pat.id,
pat.id AS flag_type
FROM post_action_types pat
)
SELECT
CASE
WHEN pat.flag_type = 3 THEN 'off_topic'
WHEN pat.flag_type = 4 THEN 'inappropriate'
WHEN pat.flag_type = 6 THEN 'notify_user'
WHEN pat.flag_type = 7 THEN 'notify_moderators'
WHEN pat.flag_type = 8 THEN 'spam'
END "type",
pa.post_id as flagged_post_id,
pd.poster_id as poster_user_id,
fd.flagger_id as flagger_user_id,
DATE(pa.created_at) as flag_created,
CASE
WHEN pa.agreed_at IS NOT NULL THEN 'agreed'
WHEN pa.disagreed_at IS NOT NULL THEN 'disagreed'
WHEN pa.deferred_at IS NOT NULL THEN 'deferred'
END "resolution",
sd.staff_id as assigned_user_id,
ROUND(pa.time_to_resolution_minutes,2) as "resolution_time (minutes)"
FROM period_actions pa
JOIN poster_data pd ON pd.id = pa.id
JOIN flagger_data fd ON fd.id = pa.id
LEFT JOIN staff_data sd ON sd.id = pa.id
JOIN flag_types pat ON pat.id = pa.post_action_type_id
ORDER BY pa.created_at ASC
Explicação da Consulta SQL
A consulta é estruturada usando Expressões de Tabela Comuns (CTEs) que preparam um subconjunto de dados que é então usado na instrução SELECT final para compilar o relatório.
Parâmetros
A consulta aceita dois parâmetros:
:start_date: O início do período para o qual gerar o relatório.:end_date: O fim do período para o qual gerar o relatório.
CTEs
period_actions
Esta CTE seleciona ações de postagem (bandeiras) que se enquadram no intervalo de datas especificado e são de tipos específicos (3, 4, 6, 7, 8), que correspondem a diferentes motivos de bandeira. Ela calcula o ‘time_to_resolution_minutes’ encontrando a diferença entre o tempo de criação da bandeira e o tempo em que ela foi respondida (concordada, discordada ou adiada).
poster_data
Esta CTE junta period_actions com as tabelas posts e users para recuperar informações sobre o autor da postagem sinalizada, incluindo seu ID de usuário, ID do tópico, número da postagem e nome de usuário.
flagger_data
Esta CTE junta period_actions com a tabela users para obter informações sobre o usuário que sinalizou a postagem, incluindo seu ID de usuário e nome de usuário.
staff_data
Esta CTE recupera informações sobre o membro da equipe que lidou com a bandeira, juntando period_actions com a tabela users com base nos IDs dos membros da equipe que concordaram, discordaram ou adiaram a bandeira.
flag_types
Esta CTE simplesmente seleciona os IDs da tabela post_action_types, que serão usados para determinar o tipo de bandeira na instrução SELECT final.
SELECT Final
A instrução SELECT final combina todas as CTEs para apresentar um relatório abrangente. Ela inclui as seguintes colunas:
type: O tipo de bandeira levantada, determinada pelo IDflag_type.flagged_post_id: O ID da postagem sinalizada.poster_user_id: O ID do usuário que postou o conteúdo sinalizado.flagger_user_id: O ID do usuário que sinalizou o conteúdo.flag_created: A data em que a bandeira foi criada.resolution: O status da resolução da bandeira (concordado, discordado, adiado).assigned_user_id: O ID do membro da equipe que lidou com a bandeira.resolution_time (minutes): O tempo necessário para resolver a bandeira, em minutos, arredondado para duas casas decimais.
O relatório é então ordenado pela data de criação da bandeira (pa.created_at) em ordem crescente.
Resultados de Exemplo
| type | flagged_post | poster_user | flagger_user | flag_created | resolution | assigned_user | resolution_time (minutes) |
|---|---|---|---|---|---|---|---|
| off_topic | Example_Post_Link1 | example_user1 | example_user2 | 2023-01-01 | agreed | example_user3 | 4.56 |
| inappropriate | Example_Post_Link2 | example_user1 | example_user2 | 2023-01-02 | disagreed | example_user3 | 38.76 |
| spam | Example_Post_Link3 | example_user1 | example_user2 | 2023-01-03 | deferred | example_user3 | 79.32 |
| — | — | — | — | — | — | — | — |