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:) [immagine] 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 staf…

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:

Numero medio di risposte dei membri per argomento (escluso lo staff)

Comunità Dati e reportistica

Eloïse_Barrège (Eloïse Barrège) 12 Gennaio 2024, 2:51pm 14

Ciao @JammyDodger
Pensi sia possibile avere la stessa cosa ma solo per gli argomenti (=nuova discussione creata) per favore?
Grazie mille!

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

Numero medio di risposte dei membri per argomento (escluso lo staff)

Argomenti correlati