User posts by email domain over last 7 days

Data & reporting
4

I think it actually gives inflated numbers due to the group joins (also, everyone is part of an automatic group for their trust level, so there’s a bit of inconsistency there). I think we can strip out those and maybe add a couple of tweaks to zone in topics, posts, and PMs.

Let me have a bash.

Maybe something like this:

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


SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extracts the domain part of the 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 -- Ensures we're using the primary email
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL :interval -- Filters posts from the last x time based on the parameter
  AND p.user_id > 0 -- Excludes system users (system and 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 -- Sorts results by the count of posts in descending order
2 Likes