Esta es una consulta SQL del Explorador de Datos diseñada para proporcionar información sobre la eficiencia y la capacidad de respuesta de los moderadores del foro al manejar las marcas planteadas por los usuarios en las publicaciones.
Este informe muestra un recuento del número de marcas manejadas y el tiempo promedio para resolver las marcas por cada moderador en un sitio, durante un rango de fechas especificado.
Comprender la rapidez y eficacia con la que los moderadores responden a las marcas puede ayudar a los administradores a garantizar que el foro siga siendo un espacio seguro y acogedor para todos los usuarios.
-- [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 de resolución en minutos
FROM post_actions pa
WHERE pa.post_action_type_id IN (3,4,6,7,8) -- Tipos de marca
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
Explicación de la consulta SQL
Parámetros
La consulta utiliza dos parámetros para definir el rango de fechas para el cual se analizan los datos:
:start_date: La fecha de inicio del período que desea analizar, en formatoYYYY-MM-DD.:end_date: La fecha de finalización del período que desea analizar, también en formatoYYYY-MM-DD.
CTEs Explicadas
- period_actions: Esta CTE selecciona las marcas planteadas dentro del período de tiempo especificado, centrándose en tipos de marca específicos. Calcula el tiempo necesario para resolver cada marca (en minutos) desde el momento en que se creó hasta que fue acordada, rechazada o aplazada por un moderador.
- moderator_actions: Esta CTE filtra las acciones de
period_actionspara incluir solo aquellas que fueron respondidas por un moderador. Identifica al moderador responsable de cada acción. - moderator_stats: Esta CTE agrega los datos de
moderator_actions, calculando el número total de marcas manejadas por cada moderador y el tiempo promedio de resolución (en minutos) de las marcas que manejaron.
Resultados
La salida final de la consulta proporciona una lista de moderadores ordenados por el número de marcas que han manejado, con una ordenación secundaria por su tiempo promedio de resolución (en orden ascendente). Para cada moderador, el informe muestra:
moderator_username: El nombre de usuario del moderador.handled_flags: El número total de marcas manejadas por el moderador dentro del período de tiempo especificado.avg_resolution_time_minutes: El tiempo promedio que tardó el moderador en resolver una marca, redondeado a dos decimales.
Resultados de ejemplo
| moderator_username | handled_flags | avg_resolution_time_minutes |
|---|---|---|
| Moderator1 | 343 | 39.41 |
| Moderator2 | 70 | 30.51 |
| Moderator3 | 63 | 42.21 |