First of all, happy new year !! 
I would like to know the SQL query to use to obtain the average number of replies per topic made by users (excluding admins) per month.
Otherwise a way to have a ratio of the total number of posts published by members VS the total number of posts published by the staff per month.
Thank you !
Happy New Year (a little belated 
)
For the average, are you looking for topics that are created by both staff and non-staff, but the reply count only from non-staff posts? And are you looking to only exclude admin, or admin and moderators?
Hi Jammy !
Exaclty, the query can look all the topics (created by both staff and non-staff), but the reply count only from non-staff posts.
For know we can only exclude admin (as I launch my community, admins & moderators are the same :))
But it will be also nice to have easily the ratio between topics from staff VS topics from non-staff (excluding admin).
I think something like this would give you the numbers youâre after:
-- [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"
Which would give you something like this:
And a bit of blurb thrown in for good measure:
This query is designed to provide a monthly statistical summary of forum activity, specifically focusing on the distinction between contributions from âstaffâ and ânon-staffâ users within a given time frame. The metrics calculated include the total number of topics created, all posts made, the number of unique non-staff users who posted, the count and percentage of posts made by non-staff, the average number of non-staff posts per topic, as well as the matching figures for staff members. The information is intended to give insights into user engagement, content generation, and the participation rate of staff versus non-staff members in the forumâs discussions. The query ensures accuracy by only considering âregularâ (non-PM) topics and excludes any deleted posts or topics, whispers/small posts/moderator actions, and posts by system users, within the specified date range.
For this one, the âstaffâ criteria is that theyâre in the automatic @staff group, which includes both admins and mods - though this can be adjusted to just target the admins only, or even a custom group of employees who arenât technically âsite staffâ as such. If you definitively want to exclude moderators you can swop that group_id at the start to â1â instead. 
Is that the kind of thing youâre looking for?
For some strange reason I know that id of staff is three. But how could one find that id? First I was totally sure that is baked in urls, like every? other ids, but no. There is only name in use.
I know so little SQL that we can easily say I canât, but this shows every group-id
select
id,
name
from
groups
But surely there is more common way to find it, isnât there?
Personally, I really want a group_id parameter lookup just like the user_id one 
- Wishlist: param dropdown for data explorer query
But until that dream comes true I use the json of the groups page to find it out eg. https://meta.discourse.org/g.json
You can do a group look-up within the query itself so it works on group names, which may be a more user-friendly way of doing it. So something like:
-- [params]
-- string :group_name
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = LOWER(:group_name))
(or the hard-coded version if you didnât want a parameter:)
SELECT user_id, true as is_staff
FROM group_users
WHERE group_id = (SELECT id FROM groups WHERE name = 'admins')
Thank you very much ! Maybe I missed something but when I click on âExecuteâ, I have this error :
Ah yes, Thatâs a quirk I should have mentioned. If you refresh your page the parameter input boxes should show up.
I bet you made that snippet on the fly. Because it doesnât work totally nice 
It shows every user from wanted group and claims everyone is is_staff 
But thanks! I got valuable info for basic level admins, about json and how to use SQL ( really, but still I like to see how AI-report sees thatâŚ)
In that example, the is_staff bit is part of the function of this particular query. Itâs specifically added here SELECT user_id, true as is_staff rather than coming from the database itself. It sets anyone from the group you designate as âstaffâ so they can be split into the two result sets (staff posts versus non-staff posts).
So if you had a group for âemployeesâ, which wouldnât technically be site staff as the database sets them, you could still add them and they would go in the âstaffâ bucket and not the ânon-staffâ bucket.
OMG this is absolutely what I needed, thank you very much !
To be sure : does âPostsâ compile topics + replies, or does it only count replies?
Thank you again !
For this one, âpostsâ does not include the first post of the topic, so is only the replies.
Hi @JammyDodger
Do you think itâs possible to have the same but only for topics (=new thread created) please?
Thank you so much !
Do you mean a ratio between topics created by staff versus non-staff added to this query?
Oh yes in the same query it would be awesome !
I think adding in these columns should do it:
-- [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"
Thank you so much, this is perfect !
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
