Este informe del Explorador de Datos está diseñado para analizar los tiempos de respuesta promedio de los miembros del personal (administradores y moderadores) a los temas creados dentro de un rango de fechas y categoría especificados.
Este informe puede ayudar a comprender la eficiencia y la capacidad de respuesta de las interacciones del personal en un sitio de Discourse, lo que puede ser crucial para la gestión de la comunidad.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false
WITH filtered_topics AS (
SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
FROM topics t
WHERE
(
':categories' = '0'
OR t.category_id IN
(
SELECT id
FROM categories
WHERE id IN(:categories)
OR (:include_subcategories AND parent_category_id IN(:categories))
)
)
AND t.created_at >= :start_date
AND t.created_at < :end_date
AND t.archetype = 'regular'
AND t.deleted_at IS NULL
),
staff_replies AS (
SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE (u.admin = true OR u.moderator = true)
AND p.deleted_at IS NULL
AND p.post_type = 1
GROUP BY p.topic_id
),
response_times AS (
SELECT
ft.topic_id,
ft.created_at AS topic_created_at,
sr.first_staff_reply,
ft.category_id as category_id,
EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
FROM filtered_topics ft
LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT
topic_id,
category_id,
topic_created_at,
response_time_hours as staff_response_time_hours
FROM response_times
ORDER BY topic_created_at ASC
Explicación de la consulta SQL
El informe utiliza varias Expresiones Comunes de Tabla (CTE) para desglosar la consulta:
filtered_topics: Esta CTE filtra los temas basándose en los parámetros de entrada como el rango de fechas, la categoría y si se deben incluir subcategorías. Asegura que solo se consideren temas de arquetipo regular no eliminados.staff_replies: Esta CTE identifica la primera respuesta realizada por un miembro del personal (administrador o moderador) a los temas identificados en la CTEfiltered_topics. Filtra las publicaciones eliminadas y considera solo las publicaciones principales (post_type = 1).response_times: Esta CTE calcula el tiempo de respuesta encontrando la diferencia entre el tiempo de creación del tema y el tiempo de la primera respuesta del personal. El resultado se convierte de segundos a horas.
La declaración SELECT final de la CTE response_times recupera el ID del tema, el ID de la categoría, la fecha de creación del tema y el tiempo de respuesta del personal calculado en horas, ordenando los resultados por la fecha de creación del tema.
Parámetros
start_date(date): La fecha de inicio del período para el cual analizar la creación de temas.end_date(date): La fecha de finalización del período para el cual analizar la creación de temas.categories(int_list): Una lista de IDs de categorías para filtrar los temas. Si se establece en 0, se incluyen todas las categorías.include_subcategories(boolean): Un indicador para determinar si se deben incluir las subcategorías de las categorías especificadas en el análisis.
Resultados
topic_id: El identificador único del tema.category_id: La categoría a la que pertenece el tema.topic_created_at: La fecha en que se creó el tema.staff_response_time_hours: El tiempo que tardó el personal en responder al tema en horas. Un valor NULL indica que no hay respuesta del personal al tema.
Resultados de ejemplo
| topic_id | category_id | topic_created_at | staff_response_time_hours |
|---|---|---|---|
| 101 | 5 | 2024-01-02 | 1.5 |
| 102 | 5 | 2024-01-02 | 3.2 |
| 103 | 12 | 2024-01-03 | NULL |
| 104 | 12 | 2024-01-04 | 0.75 |