Ce rapport Data Explorer est conçu pour analyser les temps de réponse moyens des membres du personnel (administrateurs et modérateurs) aux sujets créés dans une plage de dates et une catégorie spécifiées.
Ce rapport peut aider à comprendre l’efficacité et la réactivité des interactions du personnel sur un site Discourse, ce qui peut être crucial pour la gestion de la communauté.
-- [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
Explication de la requête SQL
Le rapport utilise plusieurs expressions de table communes (CTE) pour décomposer la requête :
filtered_topics: Cette CTE filtre les sujets en fonction des paramètres d’entrée tels que la plage de dates, la catégorie et l’inclusion ou non des sous-catégories. Elle garantit que seuls les sujets de type « régulier » non supprimés sont pris en compte.staff_replies: Cette CTE identifie la première réponse apportée par un membre du personnel (administrateur ou modérateur) aux sujets identifiés dans la CTEfiltered_topics. Elle exclut les publications supprimées et ne prend en compte que les publications principales (post_type = 1).response_times: Cette CTE calcule le temps de réponse en calculant la différence entre l’heure de création du sujet et l’heure de la première réponse du personnel. Le résultat est converti de secondes en heures.
La dernière instruction SELECT de la CTE response_times récupère l’ID du sujet, l’ID de la catégorie, la date de création du sujet et le temps de réponse du personnel calculé en heures, en triant les résultats par date de création du sujet.
Paramètres
start_date(date) : La date de début de la période pour laquelle analyser la création des sujets.end_date(date) : La date de fin de la période pour laquelle analyser la création des sujets.categories(int_list) : Une liste d’identifiants de catégories pour filtrer les sujets. Si défini sur 0, toutes les catégories sont incluses.include_subcategories(boolean) : Un indicateur pour déterminer s’il faut inclure les sous-catégories des catégories spécifiées dans l’analyse.
Résultats
topic_id: L’identifiant unique du sujet.category_id: La catégorie à laquelle appartient le sujet.topic_created_at: La date à laquelle le sujet a été créé.staff_response_time_hours: Le temps nécessaire au personnel pour répondre au sujet, en heures. Une valeur NULL indique qu’il n’y a pas de réponse du personnel au sujet.
Exemple de résultats
| 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 |