Hi all, Do you happen to know the Data Explorer Query that helps to see the number and percentage of staff posts/topics within certain time period? Thank you for all the help.

I adjusted the query and added the group name as an input parameter, this way it will work for any group. I hope it helps. --[params] --date :start_date = 2022-01-01 --date :end_date = 2023-01-01 --string :group_name = staff WITH group_filter AS ( SELECT user_id FROM group_users gu JOIN groups g ON g.id = gu.group_id WHERE LOWER(g.name) LIKE '%'||:group_name||'%' ), posts_data AS ( SELECT COUNT(*) AS total_posts, SUM(CASE WHEN gf.user_id ISNULL THEN 0 ELSE 1 END) AS staff_posts FROM posts p LEFT JOIN group_filter gf ON gf.user_id = p.user_id WHERE p. post_type = 1 AND p.deleted_at ISNULL AND p.created_at BETWEEN :start_date AND :end_date AND p.user_id > 0 ) SELECT total_posts, staff_posts, ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %" FROM posts_data

I hope the following query helps you with that. --[params] --date :start_date --date :end_date WITH posts_data AS ( SELECT COUNT(*) AS total_posts, SUM(CASE WHEN u.admin = 't' OR u.moderator = 't' THEN 1 ELSE 0 END) AS staff_posts FROM posts p INNER JOIN users u ON u.id = p.user_id WHERE p. post_type = 1 AND p.deleted_at ISNULL AND p.created_at BETWEEN :start_date AND :end_date AND p.user_id > 0 ) SELECT total_posts, staff_posts, ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %" FROM posts_data total_posts staff_posts staff_posts % 7400 3108 42.0

Hi @michebs, Thank you for sharing this. Can I ask if we can have this applied for specific group created by us? Let’s say we have a group of staff but not all of them are moderators or admins, so we need to create a new group called “Katalon_team”, can we get the data of all people in that Katalon team? Thank you.

Hi Michelle, Thank you for the updated SQL. I got a problem running it as image attached. [Screen Shot 2022-07-29 at 14.38.34] Please advise how I would resolve this. Thank you.

This error seems to be linked to the absence of the last SELECT, line 27. SELECT total_posts, staff_posts, ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %" FROM posts_data Does this make sense?

Data Explorer שאילתה - כיסוי צוות

קהילה נתונים ודיווח

Vu_Tran_Nguyen (Vu Tran Nguyen) 29 ביולי,‏ 2022,‏ 9:59am 7

Yes, sorry my mistake, thank you so much for your help.

נושא		תגובות	צפיות
Average number of members' replies per topic (excluding staff) Data & reporting	16	883	15 בינואר,‏ 2024
How can I count posts in last month by a specific group of users? Data & reporting sql-query	4	1946	9 בנובמבר,‏ 2016
Dashboard Report - Moderator Activity Data & reporting sql-query , dashboard-reports , dashboard-sql	5	976	30 באוקטובר,‏ 2024
Dashboard Report - Posts Data & reporting sql-query , dashboard-reports , dashboard-sql	3	772	9 בספטמבר,‏ 2025
Stats for group members Data & reporting sql-query	4	357	18 ביולי,‏ 2018

Data Explorer שאילתה - כיסוי צוות

נושאים קשורים