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