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) 1월 12, 2024, 2:51오후 14

안녕하세요 @JammyDodger
같은 기능을 주제(=새로운 스레드 생성)에만 적용하는 것이 가능할까요?
정말 감사합니다!

글		댓글	조회수
Data Explorer Query - Staff coverage Data & reporting sql-query	6	517	7월 29, 2022
How to show non-staff posts with zero replies Support	2	736	11월 16, 2022
Help finding some monthly site stats about topics and user trust levels Data & reporting sql-query	20	5777	12월 20, 2018
Is there a way to Search by Posts with No Staff replies on it Data & reporting sql-query	4	1663	8월 17, 2019
Average Staff Response Times to Topics Data & reporting sql-query	3	303	5월 3, 2024

주제별 멤버 평균 답변 수 (관리자 제외)

Related topics