Comment obtenir le nombre de mentions des groupes sur l'année écoulée et le nombre de membres

Communauté Données et rapports
,
1

Bonjour, je suis modérateur sur le forum Hopscotch et je me demandais si nous pouvions demander de l’aide pour ces requêtes Data Explorer :

  • Combien de mentions @ les groupes ont eues au cours des 365 derniers jours
    • triées de la plus faible à la plus élevée,
    • et uniquement si le groupe existe depuis plus de 365 jours (c’est-à-dire que la date de création du groupe remonte à plus de 365 jours)
  • Combien de mentions @ les groupes ont eues au cours des 365 derniers jours
    • uniquement si le groupe a moins de 5 mentions au cours des 365 derniers jours
    • triées de la plus faible à la plus élevée,
    • et uniquement si le groupe existe depuis plus de 365 jours (c’est-à-dire que la date de création du groupe remonte à plus de 365 jours)
  • Combien de membres les groupes ont,
    • uniquement si le groupe a moins de 10 membres
    • triées de la plus faible à la plus élevée

L’objectif est de nous permettre de supprimer les groupes inactifs. (Je transmettrais ces requêtes à un administrateur pour qu’il les crée)

Merci d’avance,
Tracey

(J’aimerais ajouter le tag data-explorer, mais il semble que je ne puisse pas le faire pour le moment)

1 « J'aime »
2

Bonjour,

Pour clarifier, avez-vous besoin de trois requêtes ?

  1. Pour tous les groupes âgés de plus de 365 jours, listez le nombre de mentions qu’ils ont eues, par ordre croissant.
  2. Pour tous les groupes âgés de plus de 365 jours, listez le nombre de mentions qu’ils ont eues, par ordre croissant, uniquement s’ils ont eu moins de cinq mentions.
  3. Pour tous les groupes comptant moins de 10 membres, listez le nombre de membres qu’ils ont.

Si c’est bien le cas, je peux le faire pour vous : toutes les informations dont vous avez besoin sont stockées dans les tables groups, group_users et group_mentions.

3

À ma connaissance, Discourse ne suit pas explicitement les mentions, mais il conserve bien la trace des liens ajoutés aux sujets. Une mention de groupe dans un message est enregistrée comme valeur du champ url dans la table topic_links, sous la forme suivante : "/groups/<nom_du_groupe>". La requête suivante devrait vous donner un résultat proche de ce que vous recherchez. Vous devrez fournir des valeurs pour les paramètres start_date et end_date. Ces dates doivent être au format aaaa-mm-jj. Par exemple, pour obtenir des données sur l’année écoulée, saisissez 2020-01-01 comme start_date et 2020-12-31 comme end_date :

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

La requête ne retourne que les mentions ajoutées aux messages réguliers (elle inclut toutefois les mentions ajoutées aux messages secrets). Si vous souhaitez que les mentions ajoutées aux messages privés soient incluses dans les résultats, supprimez la ligne AND t.archetype = 'regular' de la première requête.

Faites-moi savoir si cela ne retourne pas le type de données que vous recherchez. Je tâcherai de répondre à vos autres questions demain, si personne ne le fait avant.

3 « J'aime »