First of all, happy new year !! :tada: 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…

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):…

Happy New Year :tada: (a little belated :slight_smile:) [image] Eloïse Barrège: 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. For the average, are you looking for topics that are created by both staff a…

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 …

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, …

Personally, I really want a group_id parameter lookup just like the user_id one :crossed_fingers: :slight_smile: - Param dropdown for group_id in 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 [group_id fr…

Thank you very much ! Maybe I missed something but when I click on “Execute”, I have this error : [Capture d’écran 2024-01-10 à 17.19.19] how can i do ?

Ah yes, That’s a quirk I should have mentioned. If you refresh your page the parameter input boxes should show up. :+1:

I bet you made that snippet on the fly. Because it doesn’t work totally nice :wink: It shows every user from wanted group and claims everyone is is_staff :sweat_smile: 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-rep…

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 p…

For this one, ‘posts’ does not include the first post of the topic, so is only the replies. :+1:

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

Community Building Data & reporting

Eloïse_Barrège (Eloïse Barrège) January 10, 2024, 4:28pm 12

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 !

Topic		Replies	Views
Data Explorer Query - Staff coverage Data & reporting sql-query	6	517	July 29, 2022
How to show non-staff posts with zero replies Support	2	736	November 16, 2022
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	20	5777	December 20, 2018
Is there a way to Search by Posts with No Staff replies on it Data & reporting sql-query	4	1663	August 17, 2019
Average Staff Response Times to Topics Data & reporting sql-query	3	303	May 3, 2024

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

Related topics