Rapport Tableau de Bord - Nouveaux Contributeurs

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les nouveaux contributeurs.

Ce rapport de tableau de bord SQL est conçu pour donner aux administrateurs un aperçu clair de l’engagement des utilisateurs sur leur forum Discourse en identifiant le nombre d’utilisateurs qui ont publié pour la première fois chaque jour dans une plage de dates spécifiée.

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

Explication de la requête SQL

Le rapport récupère le nombre d’utilisateurs distincts qui ont publié pour la première fois chaque jour dans la plage de dates spécifiée. Il le fait en effectuant les étapes suivantes :

Paramètres :

  • 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.

Requête interne : Détermination de la date du premier message pour chaque utilisateur

Ce rapport contient une sous-requête qui sélectionne deux colonnes de la table posts : user_id et le timestamp created_at le plus ancien (alias first_post_date). Le timestamp created_at le plus ancien représente le premier message de l’utilisateur. Cette sous-requête inclut une condition WHERE qui ne prend en compte que les messages qui n’ont pas été supprimés (deleted_at IS NULL). Enfin, elle regroupe les résultats par user_id pour s’assurer que nous ne regardons que le premier message de chaque utilisateur.

Requête principale : Comptage des utilisateurs ayant publié leur premier message

La requête principale effectue les opérations suivantes :

  • JOIN : La table posts principale (alias p) est jointe au résultat de la sous-requête (alias fp) sur user_id pour faire correspondre chaque message au premier message de l’utilisateur correspondant.
  • Filtrage par date : La clause WHERE comprend deux conditions : elle compare le timestamp created_at de chaque message avec le first_post_date de la sous-requête pour s’assurer que nous ne traitons que les premiers messages, et elle vérifie que le timestamp created_at se situe dans la plage de dates spécifiée, y compris la end_date (+1 jour pour inclure entièrement le jour de fin).
  • Agrégation : Les messages sont ensuite regroupés par date, tronqués au jour sans composante horaire (date_trunc('day', p.created_at)::date), ce qui permet de compter les utilisateurs uniques qui ont publié pour la première fois chaque jour.
  • Comptage : En utilisant COUNT(DISTINCT p.user_id), nous obtenons le nombre d’utilisateurs uniques qui ont publié pour la première fois chaque jour respectif.
  • Tri : Les résultats sont triés par jour dans l’ordre croissant (ORDER BY day) pour donner un aperçu chronologique de l’engagement des utilisateurs.

Sortie finale

Le rapport final se compose de deux colonnes :

  • day : La date d’engagement de l’utilisateur sans la composante horaire.
  • new_contributors : Le nombre d’utilisateurs distincts qui ont publié leur premier message sur le forum chaque jour.

Résultats d’exemple

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 « J'aime »