User posts by email domain over last 7 days

data & reporting
1

Query to count posts by email domain for the past 7 days, excluding automatic groups (bots, system)

SELECT
  split_part(ue.email, '@', 2) AS email_domain, -- Extracts the domain part of the 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 -- Ensures we're using the primary 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' -- Filters posts from the last 7 days
  AND g.id IS NULL -- Ensures that users are not part of any automatic groups
GROUP BY
  email_domain
ORDER BY
  post_count DESC -- Sorts results by the count of posts in descending order
2 Likes
2

This doesn’t seem right. What’s the intention?

Count posts from the last week, grouped by email domain, but exclude system users?

Does it need to count deleted posts or posts from deleted topics, exclude small action posts, or those in PMs?

3

This was a quick and dirty test of the SQL Helper bot to find the count of posts by domain. The use-case might be for associations or membership forums who want to track which subgroups within the forum are most engaged.

It could certainly use refinement, such as removing deleted or small actions posts, PMs, etc, but I figured I’d post it in case anyone could be inspired by it.

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