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

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

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?

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.

用户过去7天按电子邮件域名的帖子

社区数据与报告

话题		回复	浏览量
User stats plus email Data & reporting sql-query	3	178	2024 年7 月 24 日
User participation Data & reporting sql-query	0	235	2017 年11 月 17 日
Who has been sending the most messages in the last week? Data & reporting sql-query	0	192	2017 年6 月 5 日
Replies via email in reports Feature	1	509	2024 年10 月 17 日
Replies via email not counted as posts? Product Support badges , email , users	0	199	2025 年8 月 30 日

用户过去7天按电子邮件域名的帖子

相关话题