Report Dashboard - Tempo alla prima risposta

Dati e reportistica
, ,
1

Questa è una versione SQL del report di dashboard per il tempo di prima risposta.

Questo report di dashboard fornisce informazioni sul tempo medio di risposta agli argomenti all’interno di un intervallo di date specificato. Il report calcola il tempo impiegato per la prima risposta pubblicata da qualcuno diverso dal creatore dell’argomento.

-- [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 di prima risposta per 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 di post regolare
    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

Spiegazione della query SQL

Questo report misura la rapidità con cui gli argomenti ricevono la loro prima risposta significativa mediante:

  • Calcolo del tempo di risposta: Per ogni argomento, trova il primo post che:
    • Non proviene dal creatore originale dell’argomento
    • Ha un numero di post > 1 (non il post iniziale)
    • È un post regolare (tipo di post = 1)
    • È stato creato dopo l’argomento (differenza di tempo positiva)
    • Non è stato eliminato
  • Raggruppamento per data: Aggrega questi tempi di risposta per la data di creazione dell’argomento
  • I risultati mostrano:
    • “date”: La data di creazione degli argomenti
    • “hours”: Tempo medio (in ore) fino alla prima risposta per gli argomenti creati in quella data
  • Esclusioni:
    • I messaggi privati sono esclusi
    • Gli argomenti e i post eliminati sono esclusi
    • Le auto-risposte sono escluse

Parametri

  • :start_date (data) - formato AAAA-MM-GG
    • Data di inizio per il periodo di riferimento
    • Saranno inclusi solo gli argomenti creati in questa data o successivamente
  • :end_date (data) - formato AAAA-MM-GG
    • Data di fine per il periodo di riferimento
    • Saranno inclusi solo gli argomenti creati prima di questa data
  • :category_id (category_id, nullable)
    • Se fornito, filtra i risultati per una categoria specifica
    • Se nullo, include argomenti da tutte le categorie
  • :include_subcategories (boolean) - Predefinito: false
    • Se true E viene fornito un category_id, include argomenti da tutte le sottocategorie
    • Se false, include solo argomenti dalla categoria specificata esatta
  • :user_ids (user_id, nullable)
    • Se fornito, include solo risposte da utenti specifici
    • Se nullo, include risposte da tutti gli utenti
    • Può accettare più ID utente come elenco separato da virgole

Risultati di esempio

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 Mi Piace