متوسط عدد ردود الأعضاء لكل موضوع (باستثناء الموظفين)

[JammyDodger]

أعتقد أن شيئًا كهذا سيعطيك الأرقام التي تبحث عنها:

-- [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 "الشهر",
    ms.total_topics AS "إجمالي المواضيع",
    ms.total_posts AS "إجمالي المشاركات",
    ms.non_staff_posts AS "مشاركات غير الموظفين",
    ROUND(ms.non_staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "مشاركات غير الموظفين (% من الإجمالي)",
    ms.non_staff_posts / NULLIF(ms.total_topics, 0) AS "متوسط مشاركات غير الموظفين لكل موضوع",
    ms.non_staff_users AS "المستخدمون غير الموظفين الذين نشروا",
    ms.staff_posts AS "مشاركات الموظفين",
    ROUND(ms.staff_posts * 100.0 / NULLIF(ms.total_posts, 0),1) || '%' AS "مشاركات الموظفين (% من الإجمالي)",
    ms.staff_posts / NULLIF(ms.total_topics, 0) AS "متوسط مشاركات الموظفين لكل موضوع",
    ms.staff_users AS "المستخدمون الموظفون الذين نشروا"
FROM month_stats ms
ORDER BY "الشهر"

مما سيعطيك شيئًا كهذا:

وقليل من الشرح الإضافي:

تم تصميم هذا الاستعلام لتوفير ملخص إحصائي شهري لنشاط المنتدى، مع التركيز بشكل خاص على التمييز بين مساهمات المستخدمين “الموظفين” و “غير الموظفين” خلال إطار زمني معين. تشمل المقاييس المحسوبة العدد الإجمالي للمواضيع التي تم إنشاؤها، وجميع المشاركات التي تم إجراؤها، وعدد المستخدمين غير الموظفين الفريدين الذين نشروا، وعدد ونسبة المشاركات التي أجراها غير الموظفين، ومتوسط عدد مشاركات غير الموظفين لكل موضوع، بالإضافة إلى الأرقام المطابقة للموظفين. تهدف المعلومات إلى تقديم رؤى حول تفاعل المستخدمين، وإنشاء المحتوى، ومعدل مشاركة الموظفين مقابل غير الموظفين في مناقشات المنتدى. يضمن الاستعلام الدقة من خلال النظر فقط في المواضيع “العادية” (غير الخاصة) واستبعاد أي مشاركات أو مواضيع محذوفة، أو همسات/مشاركات صغيرة/إجراءات مشرف، ومشاركات من قبل مستخدمي النظام، ضمن النطاق الزمني المحدد.

بالنسبة لهذا، فإن معيار “الموظفين” هو أنهم في المجموعة التلقائية @staff، والتي تشمل كلاً من المسؤولين والمشرفين - على الرغم من أنه يمكن تعديل ذلك لاستهداف المسؤولين فقط، أو حتى مجموعة مخصصة من الموظفين الذين ليسوا “موظفي موقع” تقنيًا. إذا كنت تريد بالتأكيد استبعاد المشرفين، يمكنك استبدال group_id في البداية بـ “1” بدلاً من ذلك.

هل هذا هو النوع الذي تبحث عنه؟