Посты пользователей по домену электронной почты за последние 7 дней

Создание сообщества Данные и отчетность
1

Запрос для подсчета сообщений по домену электронной почты за последние 7 дней, исключая автоматические группы (боты, система)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Извлекает доменную часть 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 -- Гарантирует использование основного email
  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' -- Фильтрует сообщения за последние 7 дней
  AND g.id IS NULL -- Гарантирует, что пользователи не входят в автоматические группы
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Сортирует результаты по количеству сообщений в порядке убывания
2

Похоже, здесь что-то не так. В чём суть?

Нужно подсчитать количество постов за последнюю неделю, сгруппированных по домену электронной почты, но исключить системных пользователей?

Нужно ли учитывать удалённые посты или посты из удалённых тем, исключать небольшие служебные действия или сообщения в личных переписках?

3

Это был быстрый и грубый тест бота SQL Helper для подсчёта количества постов по домену. Такой сценарий может быть полезен ассоциациям или форумам для членов, которые хотят отслеживать, какие подгруппы внутри форума наиболее активны.

Конечно, его можно доработать, например, исключив удалённые посты, небольшие действия, личные сообщения и т.д., но я решил опубликовать его на случай, если кто-то почерпнёт из него вдохновение.

4

Я думаю, что на самом деле это даёт завышенные цифры из-за вступления в группы (к тому же, каждый пользователь автоматически состоит в группе в зависимости от уровня доверия, поэтому здесь есть некоторая несогласованность). Я считаю, что мы можем исключить эти данные и, возможно, внести несколько корректировок для уточнения статистики по темам, сообщениям и личным сообщениям.

Давайте попробуем.

Может быть, что-то вроде этого:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Извлекает доменную часть email
  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 -- Гарантируем использование основного email
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Фильтрует сообщения за последние x дней в зависимости от параметра
  AND p.user_id > 0 -- Исключает системных пользователей (система и боты)
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type <> 3
GROUP BY email_domain
ORDER BY "Total Posts" DESC -- Сортирует результаты по количеству сообщений в порядке убывания