Questo report di Data Explorer è progettato per analizzare i tempi medi di risposta dei membri dello staff (amministratori e moderatori) agli argomenti creati all’interno di un intervallo di date e di una categoria specificati.
Questo report può aiutare a comprendere l’efficienza e la reattività delle interazioni dello staff su un sito Discourse, il che può essere cruciale per la gestione della community.
-- [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
Spiegazione della query SQL
Il report utilizza diverse Common Table Expressions (CTE) per suddividere la query:
filtered_topics: Questa CTE filtra gli argomenti in base ai parametri di input come intervallo di date, categoria e se includere o meno le sottocategorie. Assicura che vengano presi in considerazione solo argomenti di tipo “regular” non eliminati.staff_replies: Questa CTE identifica la prima risposta fornita da un membro dello staff (amministratore o moderatore) agli argomenti identificati nella CTEfiltered_topics. Filtra i post eliminati e considera solo i post principali (post_type = 1).response_times: Questa CTE calcola il tempo di risposta trovando la differenza tra l’ora di creazione dell’argomento e l’ora della prima risposta dello staff. Il risultato viene convertito da secondi a ore.
L’istruzione SELECT finale dalla CTE response_times recupera l’ID dell’argomento, l’ID della categoria, la data di creazione dell’argomento e il tempo di risposta dello staff calcolato in ore, ordinando i risultati per data di creazione dell’argomento.
Parametri
start_date(date): La data di inizio del periodo per cui analizzare la creazione degli argomenti.end_date(date): La data di fine del periodo per cui analizzare la creazione degli argomenti.categories(int_list): Un elenco di ID di categoria per filtrare gli argomenti. Se impostato su 0, vengono incluse tutte le categorie.include_subcategories(boolean): Un flag per determinare se includere o meno le sottocategorie delle categorie specificate nell’analisi.
Risultati
topic_id: L’identificatore univoco dell’argomento.category_id: La categoria a cui appartiene l’argomento.topic_created_at: La data in cui è stato creato l’argomento.staff_response_time_hours: Il tempo impiegato dallo staff per rispondere all’argomento in ore. Un valore NULL indica che non c’è stata alcuna risposta dello staff all’argomento.
Risultati di esempio
| 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 |