トピックあたりのメンバーの平均返信数（スタッフを除く）

コミュニティ データとレポート
5

これならお探しの数値が得られると思います。

-- [params]
-- date :start_date
-- date :end_date


WITH staff_data AS (

    SELECT user_id, true as is_staff
    FROM group_users
    WHERE group_id = 3
),

month_stats AS (

    SELECT
        date_trunc('month', p.created_at)::date AS month,
        COUNT(*) FILTER (WHERE p.post_number = 1) AS total_topics,
        COUNT(*) FILTER (WHERE p.post_number <> 1) AS total_posts,
        COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_users,
        COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff IS NOT TRUE) AS non_staff_posts,
        COUNT(DISTINCT p.user_id) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_users,
        COUNT(*) FILTER (WHERE p.post_number <> 1 AND is_staff) AS staff_posts
    FROM posts p
      LEFT JOIN topics t ON t.id = p.topic_id
      LEFT JOIN staff_data s ON p.user_id = s.user_id
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
      AND t.archetype = 'regular'
      AND t.deleted_at IS NULL
      AND p.deleted_at IS NULL
      AND p.post_type = 1
      AND p.user_id > 0
    GROUP BY month
)

SELECT
    ms.month AS "Month",
    ms.total_topics AS "All Topics",
    ms.total_posts AS "All Posts",
    ms.non_staff_posts AS "Non-staff posts",
    ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Non-staff posts (% of total)",
    ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "Average non-staff posts per topic",
    ms.non_staff_users AS "Non-staff users who posted",
    ms.staff_posts AS "Staff posts",
    ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "Staff posts (% of total)",
    ms.staff_posts / NULLIF(ms.total_topics, 0) AS "Average staff posts per topic",
    ms.staff_users AS "Staff users who posted"
FROM month_stats ms
ORDER BY "Month"

これにより、次のような結果が得られます。

Staff and non-staff posts (monthly)
Staff and non-staff posts (monthly)1396×712 24.7 KB

おまけとして、少し解説を付け加えます。 :slight_smile:

このクエリは、指定された期間における「スタッフ」と「非スタッフ」ユーザーの貢献を区別することに焦点を当てた、月次統計サマリーを提供するように設計されています。計算される指標には、作成されたトピックの総数、投稿されたすべての投稿数、投稿したユニークな非スタッフユーザー数、非スタッフによって投稿された投稿の数と割合、トピックあたりの平均非スタッフ投稿数、およびスタッフメンバーに関する同様の数値が含まれます。この情報は、ユーザーエンゲージメント、コンテンツ生成、およびフォーラムのディスカッションにおけるスタッフと非スタッフメンバーの参加率に関する洞察を提供することを目的としています。クエリは、「通常の」（PMではない）トピックのみを考慮し、指定された日付範囲内で削除された投稿やトピック、ささやき/短い投稿/モデレーターアクション、およびシステムユーザーによる投稿を除外することで精度を保証します。

この場合、「スタッフ」の基準は、管理者とモデレーターの両方を含む自動化された @staff グループに属していることですが、管理者にのみターゲットを絞ったり、技術的には「サイトスタッフ」ではない従業員のカスタムグループをターゲットにしたりすることも調整可能です。モデレーターを確実に除外したい場合は、先頭の group_id を ‘1’ に切り替えることができます。 :+1:

お探しのものに近いでしょうか？