Gli utenti pubblicano per dominio email negli ultimi 7 giorni

Dati e reportistica
1

Query per contare i post per dominio email negli ultimi 7 giorni, escludendo i gruppi automatici (bot, sistema)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Estrae la parte del dominio dell'email
  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 -- Assicura che stiamo usando l'email primaria
  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' -- Filtra i post degli ultimi 7 giorni
  AND g.id IS NULL -- Assicura che gli utenti non facciano parte di gruppi automatici
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Ordina i risultati per conteggio dei post in ordine decrescente
2 Mi Piace
2

Questo non sembra giusto. Qual è l’intenzione?

Conta i post dell’ultima settimana, raggruppati per dominio email, ma escludi gli utenti di sistema?

È necessario contare i post eliminati o i post da argomenti eliminati, escludere i post di piccole azioni o quelli nei PM?

3

Questo è stato un test rapido e grezzo del bot SQL Helper per trovare il conteggio dei post per dominio. Il caso d’uso potrebbe essere per associazioni o forum di appartenenza che desiderano monitorare quali sottogruppi all’interno del forum sono più attivi.

Certamente potrebbe essere perfezionato, ad esempio rimuovendo post eliminati o di piccole azioni, messaggi privati, ecc., ma ho pensato di pubblicarlo nel caso in cui qualcuno potesse trarne ispirazione.

4

Penso che in realtà fornisca numeri gonfiati a causa delle adesioni ai gruppi (inoltre, tutti fanno parte di un gruppo automatico per il loro livello di fiducia, quindi c’è un po’ di incoerenza lì). Penso che possiamo eliminare quelli e magari aggiungere un paio di modifiche per concentrarci su argomenti, post e PM.

Lasciami provare.

Forse qualcosa del genere:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Estrae la parte del dominio dell'email
  COUNT(p.id) AS "Post totali",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "Argomenti",
  COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "Post",
  COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "Post messaggi privati"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- Assicura che stiamo usando l'email primaria
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filtra i post degli ultimi x tempo in base al parametro
  AND p.user_id > 0 -- Esclude gli utenti di sistema (sistema e bot)
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "Post totali" DESC -- Ordina i risultati per conteggio dei post in ordine decrescente
2 Mi Piace
5

2 post sono stati divisi in un nuovo argomento: Statistiche utente più email

6

Questo argomento è stato chiuso automaticamente 30 giorni dopo l’ultima risposta. Non sono più consentite nuove risposte.