Rapport de tableau de bord - Utilisateur à utilisateur

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les messages privés entre utilisateurs (avec réponses) et entre utilisateurs (sans réponses).

Ce rapport compte le nombre de messages privés envoyés par les utilisateurs chaque jour, dans une plage de dates spécifiée, fournissant un aperçu de l’activité d’engagement des utilisateurs dans la communication privée.

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Explication de la requête SQL

Cette requête SQL effectue les actions suivantes :

  • Paramètres :
    • :start_date et :end_date, qui sont utilisés pour filtrer les données dans la plage de dates spécifiée. Les deux paramètres de date acceptent le format AAAA-MM-JJ.
    • :include_replies pour déterminer si les réponses aux messages initiaux doivent être incluses dans le décompte. Défini sur false par défaut pour refléter le comportement du rapport Utilisateur à Utilisateur (sans réponses). La définition de ce paramètre sur true reflétera le comportement du rapport Utilisateur à Utilisateur (avec réponses).
  • Sélection des données :
    • La requête sélectionne la date created_at de la table posts, qui représente la date de création de chaque message.
  • Opération de jointure :
    • La requête effectue une jointure INNER JOIN entre les tables posts et topics sur leurs champs id. Cette jointure est cruciale car elle relie chaque publication à son sujet correspondant, nous permettant de filtrer uniquement les publications qui font partie d’une conversation de message privé.
  • Critères de filtrage : La requête applique plusieurs filtres :
    • Elle ne prend en compte que les publications qui font partie d’un sujet avec un archetype de ‘private_message’, garantissant que seuls les messages privés sont comptés.
    • Elle exclut toutes les publications qui ne sont pas associées à un utilisateur en s’assurant que user_id est supérieur à 0.
    • Elle affine davantage les résultats pour les sujets avec un subtype de ‘user_to_user’, ce qui indique un message privé entre utilisateurs individuels.
    • Elle exclut toutes les publications ou sujets qui ont été supprimés en vérifiant que deleted_at est NULL pour les deux tables.
    • Elle applique le filtre de plage de dates pour inclure uniquement les publications créées entre :start_date et :end_date.
    • Si :include_replies est false, seule la première publication de chaque sujet est comptée (p.post_number = 1), excluant les réponses.
  • Agrégation : La requête regroupe les résultats par la date de création des publications, ce qui permet de compter le nombre de messages envoyés chaque jour.
  • Tri : Enfin, la requête trie les résultats par date dans l’ordre croissant, fournissant une séquence chronologique de l’activité des messages privés entre utilisateurs.

Résultats d’exemple

jour personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
4 « J'aime »