# Average number of members' replies per topic (excluding staff)

**URL:** <https://meta.discourse.org/t/average-number-of-members-replies-per-topic-excluding-staff/290618>\
**Category:** Data & reporting\
**Created:** [January 5, 2024, 11:35am UTC](https://meta.discourse.org/t/average-number-of-members-replies-per-topic-excluding-staff/290618 "2024-01-05T11:35:24Z")\
**Posts on this page:** 1\
**Showing post:** 17

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [January 13, 2024, 2:54pm UTC](https://meta.discourse.org/t/average-number-of-members-replies-per-topic-excluding-staff/290618/17 "2024-01-13T14:54:11Z")

</div>

I think adding in these columns should do it:

```sql
-- [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 "Month",
    ms.total_topics AS "All Topics",
    ms.non_staff_topics AS "Non-staff topics",
    ROUND(ms.non_staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Non-staff topics (% of total)",
    ms.staff_topics AS "Staff topics",
    ROUND(ms.staff_topics * 100.0 / NULLIF(ms.total_topics, 0),1) || '%' AS "Staff topics (% of total)",
    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"

```

 ![including staff v non-staff topic stats](https://global.discourse-cdn.com/meta/original/4X/d/9/3/d9355c2651d52e933ad5ecd977863084c691b56f.png)

---

_[View the full topic](https://meta.discourse.org/t/average-number-of-members-replies-per-topic-excluding-staff/290618)._