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

[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 AND is_staff IS NOT TRUE) AS non_staff_topics,
        COUNT(*) FILTER (WHERE p.post_number = 1 AND is_staff IS TRUE) AS staff_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.non_staff_topics AS "مواضيع غير الموظفين",
    ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "مواضيع غير الموظفين (% من الإجمالي)",
    ms.staff_topics AS "مواضيع الموظفين",
    ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' 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 "الشهر"