Rapport tableau de bord - Croissance du niveau de confiance

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour la croissance du niveau de confiance.

Ce rapport de tableau de bord compte le nombre d’utilisateurs atteignant les niveaux de confiance 1, 2, 3 et 4 chaque jour dans la plage de dates spécifiée, et aide les administrateurs à suivre l’engagement et la progression des utilisateurs à travers les niveaux de confiance dans une communauté Discourse.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

Explication de la requête SQL

La requête fonctionne en sélectionnant des enregistrements de la table user_histories, qui enregistre les changements d’état des utilisateurs, y compris leurs niveaux de confiance.

Voici une ventilation de la requête :

  • Paramètres de date : La requête accepte deux paramètres, :start_date et :end_date, qui définissent la plage de dates pour le rapport. Les deux paramètres de date acceptent le format de date AAAA-MM-JJ.
  • date(created_at) AS date : Cette partie de l’instruction SELECT extrait la date de l’horodatage created_at et l’étiquette comme « date » pour chaque enregistrement. Ceci sera utilisé pour regrouper les résultats par jour.
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached : Cette section compte le nombre d’utilisateurs qui ont progressé vers un niveau de confiance spécifique (Y) à partir d’un niveau de confiance inférieur (inférieur à X). La clause FILTER est utilisée pour inclure uniquement les lignes qui correspondent à la condition spécifiée. Le transtypage ::integer est utilisé pour convertir previous_value en entier pour la comparaison. Il y a quatre de ces colonnes de comptage, une pour chaque niveau de confiance de 1 à 4.
  • FROM user_histories : Ceci spécifie la table à partir de laquelle récupérer les données.
  • WHERE created_at >= :start_date AND created_at <= :end_date : Ceci filtre les enregistrements pour inclure uniquement ceux dans la plage de dates spécifiée.
  • AND (action = 2 OR action = 15) : Ceci filtre les enregistrements pour inclure uniquement ceux où l’action indique un changement de niveau de confiance. Les actions spécifiques qui correspondent aux changements de niveau de confiance sont représentées par les nombres 2 et 15.
  • GROUP BY date(created_at) : Ceci regroupe les résultats par date, de sorte que vous obtenez un décompte des changements de niveau de confiance pour chaque jour dans la plage spécifiée.
  • ORDER BY date(created_at) : Ceci trie les résultats chronologiquement par date.

Exemple de résultats

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 « J'aime »