Usuários que postaram por domínio de email nos últimos 7 dias

Dados e relatórios
1

Consulta para contar publicações por domínio de e-mail nos últimos 7 dias, excluindo grupos automáticos (bots, sistema)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrai a parte do domínio do 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 -- Garante que estamos usando o 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' -- Filtra publicações dos últimos 7 dias
  AND g.id IS NULL -- Garante que os usuários não façam parte de nenhum grupo automático
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Ordena os resultados pela contagem de publicações em ordem decrescente
2 curtidas
2

Isso não parece certo. Qual é a intenção?

Contar postagens da última semana, agrupadas por domínio de e-mail, mas excluir usuários do sistema?

Precisa contar postagens excluídas ou postagens de tópicos excluídos, excluir postagens de ação pequenas ou aquelas em mensagens privadas?

3

Este foi um teste rápido e improvisado do bot SQL Helper para encontrar a contagem de postagens por domínio. O caso de uso pode ser para associações ou fóruns de membros que desejam rastrear quais subgrupos dentro do fórum estão mais engajados.

Certamente poderia ser refinado, como remover postagens excluídas ou de pequenas ações, mensagens privadas, etc., mas pensei em publicá-lo caso alguém pudesse se inspirar nele.

4

Eu acho que, na verdade, ele fornece números inflados devido às junções de grupo (além disso, todos fazem parte de um grupo automático para seu nível de confiança, então há um pouco de inconsistência aí). Acho que podemos remover isso e talvez adicionar alguns ajustes para focar em tópicos, posts e MPs.

Deixe-me tentar.

Talvez algo como isto:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrai a parte do domínio do e-mail
  COUNT(p.id) AS "Total Posts",
  COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "Tópicos",
  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 "Posts de Mensagem Pessoal"
FROM posts p
  JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- Garante que estamos usando o e-mail principal
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filtra posts dos últimos x tempo com base no parâmetro
  AND p.user_id > 0 -- Exclui usuários do sistema (sistema e 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 -- Ordena os resultados pela contagem de posts em ordem decrescente
2 curtidas
5

2 posts foram divididos para um novo tópico: User Stats plus email

6

Este tópico foi fechado automaticamente 30 dias após a última resposta. Novas respostas não são mais permitidas.