I am building queries for my community to be able to fulfill some metrics, and what I am seeing is something I can’t seem to get around. The following query essentially just pulls back all posts made by staff members during a time period, but also includes activities like opening/closing a topic, etc. -- [params] -- string :start = 2018-03-04 00:00:00 -- string :end = 2018-03-10 23:59:59.999999 SELECT p.user_id, count(p.id) as Replies FROM posts p WHERE p.created_at between :start and :end AND p.user_id IN (select u.id from users u where u.primary_group_id = 41) AND p.user_id not in ('-1', '-2') AND p.topic_id not in ('4','24','26','32','33','34','35','36','37','38','39','40') GROUP BY p.user_id ORDER BY Replies DESC I see a post_action_types table but it has things like Likes, Bookmarks, etc.. I am trying to get to where I am displaying actual replies by my staff and not just things like “likes” and such

You should filter only regular posts by WHERE p.post_type = 1. Available post types are regular: 1, moderator_action: 2, small_action: 3, whisper: 4.

[image] vinothkannans: p.post_type = 1 Brilliant. Thanks mate.

[image] vinothkannans: Available post types are regular: 1, moderator_action: 2, small_action: 3, whisper: 4. What would classify as a “small_action”?

Perfect -thanks for the clarification

Query of replies made by staff includes actions like opening and closing threads

Product Support

jerdog (Jeremy M) March 8, 2018, 6:21pm 3

Brilliant. Thanks mate.

Topic		Replies	Views
How to show non-staff posts with zero replies Product Support	2	763	November 16, 2022
Average Staff Response Times to Topics Data & reporting sql-query	3	326	May 3, 2024
Is there a way to Search by Posts with No Staff replies on it Data & reporting sql-query	4	1701	August 17, 2019
Filter topics not answered by Staff Data & reporting sql-query	4	1162	November 2, 2021
Data Explorer Query - Staff coverage Data & reporting sql-query	6	556	July 29, 2022

Query of replies made by staff includes actions like opening and closing threads

Related topics