Relatório do Painel - Tempo de Primeira Resposta

Dados e relatórios
, ,
1

Este é um relatório de painel em versão SQL para Tempo de Primeira Resposta.

Este relatório de painel fornece insights sobre o tempo médio de resposta aos tópicos dentro de um intervalo de datas especificado. O relatório calcula o tempo necessário para a primeira resposta ser postada por alguém que não seja o criador do tópico.

-- [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 

-- Tempo para a primeira resposta por data
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 postagem 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

Explicação da Consulta SQL

Este relatório mede a rapidez com que os tópicos recebem sua primeira resposta significativa, ao:

  • Calcular o tempo de resposta: Para cada tópico, encontra a primeira postagem que:
    • Não é do criador original do tópico
    • Tem um número de postagem > 1 (não a postagem inicial)
    • É uma postagem regular (tipo de postagem = 1)
    • Foi criada após o tópico (diferença de tempo positiva)
    • Não foi excluída
  • Agrupar por data: Agrega esses tempos de resposta pela data em que o tópico foi criado
  • Resultados mostram:
    • “date”: A data em que os tópicos foram criados
    • “hours”: Tempo médio (em horas) até a primeira resposta para os tópicos criados nessa data
  • Exclusões:
    • Mensagens privadas são excluídas
    • Tópicos e postagens excluídos são excluídos
    • Auto-respostas são excluídas

Parâmetros

  • :start_date (data) - formato YYYY-MM-DD
    • Data de início do período do relatório
    • Apenas tópicos criados nesta data ou posteriormente serão incluídos
  • :end_date (data) - formato YYYY-MM-DD
    • Data de término do período do relatório
    • Apenas tópicos criados antes desta data serão incluídos
  • :category_id (category_id, anulável)
    • Quando fornecido, filtra os resultados para uma categoria específica
    • Quando nulo, inclui tópicos de todas as categorias
  • :include_subcategories (booleano) - Padrão: false
    • Quando true E um category_id é fornecido, inclui tópicos de todas as subcategorias
    • Quando false, inclui apenas tópicos da categoria especificada exata
  • :user_ids (user_id, anulável)
    • Quando fornecido, inclui apenas respostas de usuários específicos
    • Quando nulo, inclui respostas de todos os usuários
    • Pode aceitar vários IDs de usuário como uma lista separada por vírgulas

Resultados de Exemplo

date response_time_hours
2023-11-12 29.87
2023-11-13 81.52
2023-11-14 5.17
2023-11-15 6.51
2023-11-16 7.75
… …
3 curtidas