Publications des utilisateurs par domaine de courrier électronique au cours des 7 derniers jours

Données et rapports
1

Requête pour compter les publications par domaine de messagerie au cours des 7 derniers jours, en excluant les groupes automatiques (bots, système)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrait la partie domaine de l'e-mail
  COUNT(p.id) AS post_count
FROM
  posts p
  JOIN users u ON p.user_id = u.id
  JOIN user_emails ue ON u.id = ue.user_id
    AND ue.primary = TRUE -- S'assure que nous utilisons l'e-mail principal
  LEFT JOIN group_users gu ON gu.user_id = u.id
  LEFT JOIN GROUPS g ON gu.group_id = g.id
    AND g.automatic = TRUE
WHERE
  p.created_at >= CURRENT_DATE - INTERVAL '7 days' -- Filtre les publications des 7 derniers jours
  AND g.id IS NULL -- S'assure que les utilisateurs ne font pas partie de groupes automatiques
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Trie les résultats par le nombre de publications en ordre décroissant
2 « J'aime »
2

Cela ne semble pas correct. Quelle est l’intention ?

Compter les publications de la semaine dernière, regroupées par domaine de messagerie, mais exclure les utilisateurs système ?

Faut-il compter les publications supprimées ou les publications de sujets supprimés, exclure les publications de petite action ou celles des messages privés ?

3

Ceci était un test rapide et sale du bot SQL Helper pour trouver le nombre de publications par domaine. Le cas d’utilisation pourrait être pour les associations ou les forums de membres qui souhaitent suivre les sous-groupes les plus actifs au sein du forum.

Cela pourrait certainement être affiné, comme la suppression des publications supprimées ou des petites actions, des messages privés, etc., mais j’ai pensé le publier au cas où quelqu’un pourrait s’en inspirer.

4

Je pense que cela donne en fait des chiffres gonflés en raison des adhésions aux groupes (de plus, tout le monde fait partie d’un groupe automatique pour son niveau de confiance, il y a donc une légère incohérence). Je pense que nous pouvons supprimer cela et peut-être ajouter quelques ajustements pour cibler les sujets, les publications et les MP.

Laissez-moi essayer.

Peut-être quelque chose comme ceci :

-- [params]
-- string :interval = 7 days


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrait la partie domaine de l'e-mail
  COUNT(p.id) AS \"Total Posts\",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS \"Topics\",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS \"Posts\",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS \"Personal Message posts\"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- Garantit que nous utilisons l'e-mail principal
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filtre les publications des x derniers temps en fonction du paramètre
  AND p.user_id > 0 -- Exclut les utilisateurs système (système et bots)
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY \"Total Posts\" DESC -- Trie les résultats par le nombre de publications dans l'ordre décroissant
2 « J'aime »
5

2 messages ont été déplacées vers un nouveau sujet : Statistiques utilisateur plus e-mail

6

Ce sujet a été automatiquement fermé 30 jours après la dernière réponse. Les nouvelles réponses ne sont plus autorisées.