Esta es una versión SQL del Informe de Estado de Banderas.
El informe de Estado de Banderas está diseñado para proporcionar información sobre el proceso de moderación analizando el tiempo necesario para resolver las publicaciones marcadas dentro de un período de tiempo específico. Incluye detalles sobre el tipo de bandera planteada, la publicación y los usuarios involucrados, la fecha de creación de la bandera, el estado de resolución, el miembro del personal que manejó la bandera y el tiempo necesario para alcanzar una resolución.
Este informe es útil para los gerentes de comunidad y los moderadores para evaluar la eficiencia del equipo de moderación, identificar cualquier problema en el proceso de resolución y garantizar que los estándares de la comunidad se mantengan de manera oportuna.
-- [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 -- 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
),
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
Explicación de la consulta SQL
La consulta está estructurada utilizando Expresiones Comunes de Tabla (CTE) que preparan un subconjunto de datos que luego se utiliza en la declaración SELECT final para compilar el informe.
Parámetros
La consulta acepta dos parámetros:
:start_date: El comienzo del período para el cual generar el informe.:end_date: El final del período para el cual generar el informe.
CTEs
period_actions
Esta CTE selecciona acciones de publicación (banderas) que caen dentro del rango de fechas especificado y son de tipos específicos (3, 4, 6, 7, 8), que corresponden a diferentes razones de bandera. Calcula el ‘time_to_resolution_minutes’ encontrando la diferencia entre el tiempo de creación de la bandera y el tiempo en que se respondió (acordada, en desacuerdo o aplazada).
poster_data
Esta CTE une period_actions con las tablas posts y users para recuperar información sobre el autor de la publicación marcada, incluido su ID de usuario, ID de tema, número de publicación y nombre de usuario.
flagger_data
Esta CTE une period_actions con la tabla users para obtener información sobre el usuario que marcó la publicación, incluido su ID de usuario y nombre de usuario.
staff_data
Esta CTE recupera información sobre el miembro del personal que manejó la bandera al unir period_actions con la tabla users basándose en los ID de los miembros del personal que acordaron, no estuvieron de acuerdo o aplazaron la bandera.
flag_types
Esta CTE simplemente selecciona los ID de la tabla post_action_types, que se utilizarán para determinar el tipo de bandera en la declaración SELECT final.
SELECT final
La declaración SELECT final combina todas las CTE para presentar un informe completo. Incluye las siguientes columnas:
type: El tipo de bandera planteada, determinada por el IDflag_type.flagged_post_id: El ID de la publicación marcada.poster_user_id: El ID del usuario que publicó el contenido marcado.flagger_user_id: El ID del usuario que marcó el contenido.flag_created: La fecha en que se creó la bandera.resolution: El estado de resolución de la bandera (acordada, en desacuerdo, aplazada).assigned_user_id: El ID del miembro del personal que manejó la bandera.resolution_time (minutes): El tiempo necesario para resolver la bandera, en minutos, redondeado a dos decimales.
El informe se ordena luego por la fecha de creación de la bandera (pa.created_at) en orden ascendente.
Resultados de ejemplo
| 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 |
| — | — | — | — | — | — | — | — |