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:) [画像] 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 and …

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…

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. :+1:

トピックあたりのメンバーの平均返信数（スタッフを除く）

コミュニティデータとレポート

JammyDodger 2024 年 1 月 10 日午後 4:30 13

この場合、「投稿」にはトピックの最初の投稿は含まれず、返信のみが含まれます。

トピック		返信	表示
Data Explorer Query - Staff coverage Data & reporting sql-query	6	517	2022 年 7 月 29 日
How to show non-staff posts with zero replies Support	2	736	2022 年 11 月 16 日
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	20	5777	2018 年 12 月 20 日
Is there a way to Search by Posts with No Staff replies on it Data & reporting sql-query	4	1663	2019 年 8 月 17 日
Average Staff Response Times to Topics Data & reporting sql-query	3	303	2024 年 5 月 3 日

トピックあたりのメンバーの平均返信数（スタッフを除く）

関連トピック