Informe del panel - Tiempo de primera respuesta

Este es un informe de panel SQL para el Tiempo de Primera Respuesta.

Este informe de panel proporciona información sobre el tiempo de respuesta promedio a los temas dentro de un rango de fechas especificado. El informe calcula el tiempo que se tarda en publicar la primera respuesta por parte de alguien que no sea el creador del tema.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids 

-- Tiempo de primera respuesta por fecha
SELECT 
  t.created_at AS "date",
  CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
FROM (
  SELECT 
    t.id, 
    t.created_at::date AS created_at, 
    EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS "hours"
  FROM topics t
  JOIN posts p ON p.topic_id = t.id
  WHERE 
    t.created_at >= :start_date
    AND t.created_at < :end_date
    AND t.archetype <> 'private_message'
    AND t.deleted_at IS NULL
    AND p.deleted_at IS NULL
    AND p.post_number > 1
    AND p.user_id != t.user_id
    AND p.post_type = 1  -- Tipo de publicación regular
    AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
    AND (:category_id IS NULL OR 
         CASE WHEN :include_subcategories THEN 
           t.category_id IN (
             WITH RECURSIVE subcategories AS (
               SELECT id FROM categories WHERE id = :category_id
               UNION
               SELECT c.id FROM categories c
               JOIN subcategories sc ON sc.id = c.parent_category_id
             )
             SELECT id FROM subcategories
           )
         ELSE 
           t.category_id = :category_id
         END
        )
    AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
  GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at

Explicación de la consulta SQL

Este informe mide la rapidez con la que los temas reciben su primera respuesta significativa al:

• Calcular el tiempo de respuesta: Para cada tema, encuentra la primera publicación que:
  • No es del creador original del tema.
  • Tiene un número de publicación > 1 (no es la publicación inicial).
  • Es una publicación regular (tipo de publicación = 1).
  • Se creó después del tema (diferencia de tiempo positiva).
  • No ha sido eliminada.
• Agrupar por fecha: Agrega estos tiempos de respuesta por la fecha en que se creó el tema.
• Los resultados muestran:
  • “date”: La fecha en que se crearon los temas.
  • “hours”: Tiempo promedio (en horas) hasta la primera respuesta para los temas creados en esa fecha.
• Exclusiones:
  • Se excluyen los mensajes privados.
  • Se excluyen los temas y las publicaciones eliminadas.
  • Se excluyen las respuestas propias.

Parámetros

• :start_date (fecha) - formato YYYY-MM-DD
  • Fecha de inicio del período del informe.
  • Solo se incluirán los temas creados en o después de esta fecha.
• :end_date (fecha) - formato YYYY-MM-DD
  • Fecha de finalización del período del informe.
  • Solo se incluirán los temas creados antes de esta fecha.
• :category_id (category_id, anulable)
  • Cuando se proporciona, filtra los resultados a una categoría específica.
  • Cuando es nulo, incluye temas de todas las categorías.
• :include_subcategories (booleano) - Predeterminado: false
  • Cuando es verdadero Y se proporciona un category_id, incluye temas de todas las subcategorías.
  • Cuando es falso, solo incluye temas de la categoría especificada exacta.
• :user_ids (user_id, anulable)
  • Cuando se proporciona, solo incluye respuestas de usuarios específicos.
  • Cuando es nulo, incluye respuestas de todos los usuarios.
  • Puede aceptar varios IDs de usuario como una lista separada por comas.

Resultados de ejemplo