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

Создание сообщества Данные и отчетность
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 -- Сортирует результаты по количеству сообщений в порядке убывания