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:

平均每个主题的会员回复数（不包括工作人员）

社区数据与报告

Eloïse_Barrège (Eloïse Barrège) 2024 年1 月 10 日 16:28 12

太棒了，这正是我需要的，非常感谢！\n为了确认： “帖子”是编译主题+回复，还是只计算回复？\n\n再次感谢！

话题		回复	浏览量
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 日

平均每个主题的会员回复数（不包括工作人员）

相关话题