過去7日間のメールドメイン別ユーザー投稿

過去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 -- 結果を投稿数降順で並べ替え

2件の投稿が新しいトピックに分割されました：User Stats plus email

このトピックは、最後の返信から30日後に自動的にクローズされました。新しい返信は許可されていません。