Usuarios publican por dominio de correo electrónico en los últimos 7 días

Comunidad Datos e informes
1

Consulta para contar publicaciones por dominio de correo electrónico en los últimos 7 días, excluyendo grupos automáticos (bots, sistema)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrae la parte del dominio del correo electrónico
  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 -- Asegura que estamos usando el correo electrónico 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 las publicaciones de los últimos 7 días
  AND g.id IS NULL -- Asegura que los usuarios no formen parte de ningún grupo automático
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Ordena los resultados por el recuento de publicaciones en orden descendente
2

Esto no parece correcto. ¿Cuál es la intención?

¿Contar las publicaciones de la última semana, agrupadas por dominio de correo electrónico, pero excluyendo a los usuarios del sistema?

¿Necesita contar las publicaciones eliminadas o las publicaciones de temas eliminados, excluir las publicaciones de acciones pequeñas o las que se encuentran en mensajes privados?

3

Esta fue una prueba rápida y sucia del bot SQL Helper para encontrar el recuento de publicaciones por dominio. El caso de uso podría ser para asociaciones o foros de membresía que desean rastrear qué subgrupos dentro del foro están más comprometidos.

Ciertamente podría refinarse, como eliminar publicaciones eliminadas o de acciones pequeñas, PM, etc., pero pensé en publicarlo en caso de que alguien pudiera inspirarse en él.

4

Creo que en realidad da números inflados debido a las uniones de grupos (además, todos forman parte de un grupo automático por su nivel de confianza, por lo que hay una pequeña inconsistencia allí). Creo que podemos eliminar eso y tal vez hacer un par de ajustes para centrarnos en temas, publicaciones y mensajes privados.

Déjame intentarlo.

Quizás algo como esto:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extrae la parte del dominio del correo electrónico
  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 -- Asegura que estamos usando el correo electrónico principal
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filtra las publicaciones de los últimos x tiempo según el parámetro
  AND p.user_id > 0 -- Excluye usuarios del sistema (sistema y 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 los resultados por el recuento de publicaciones en orden descendente