過去7日間のメールドメインごとの投稿数をカウントするクエリ（自動グループ（ボット、システム）を除く）
SELECT
split_part(ue.email, '@', 2) AS email_domain, -- メールのアドレスからドメイン部分を抽出
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 -- プライマリメールを使用することを保証
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 -- 結果を投稿数で降順に並べ替え
これは正しくないようです。意図は何ですか？
先週の投稿をメールドメイン別に集計し、システムユーザーを除外しますか？
削除された投稿や削除されたトピックの投稿をカウントする必要がありますか、それとも小さなアクション投稿やプライベートメッセージ（PM）の投稿を除外する必要がありますか？
これは、ドメインごとの投稿数を検索するためのSQLヘルパーボットの迅速かつ簡易なテストでした。ユースケースとしては、フォーラム内のどのサブグループが最も活発であるかを追跡したい協会や会員フォーラムなどが考えられます。
削除済みまたは小規模なアクション投稿、PMなどを削除するなど、確かに改善の余地はありますが、誰かのインスピレーションになればと思い、まずは投稿しました。
グループ参加により、実際には数字が膨らんでいると思います（また、全員が信頼レベルに応じた自動グループに属しているため、多少の一貫性のなさがあります）。それらを削除し、トピック、投稿、およびプライベートメッセージに絞り込むためのいくつかの調整を加えることができると思います。
試してみます。
このような感じでしょうか。
-- [params]
-- string :interval = 7 days
SELECT
split_part(ue.email, '@', 2) AS email_domain, -- メールからドメイン部分を抽出
COUNT(p.id) AS "合計投稿数",
COUNT(p.id) FILTER (WHERE p.post_number = 1 AND t.archetype = 'regular') AS "トピック",
COUNT(p.id) FILTER (WHERE p.post_number > 1 AND t.archetype = 'regular') AS "投稿",
COUNT(p.id) FILTER (WHERE t.archetype = 'private_message') AS "個人メッセージ投稿数"
FROM posts p
JOIN user_emails ue ON p.user_id = ue.user_id AND ue.primary = TRUE -- プライマリメールを使用することを保証
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 "合計投稿数" DESC -- 結果を投稿数降順で並べ替え