Get count of recent flags in a particular category

Community Building Data & reporting
3

@southpaw Got me; I was about to post :smile:

If you are interested, I added custom fields you could fill in:

--[params]
-- int_list :category_ids
-- text :interval = 20min

SELECT t.category_id, count(pa.id) count
from post_actions pa
INNER JOIN posts p on p.id = pa.post_id
INNER JOIN topics t on t.id = p.topic_id
WHERE post_action_type_id in (3, 4, 6, 7, 8)
    AND t.category_id in (:category_ids)
    AND pa.created_at >= now() - interval :interval
GROUP BY t.category_id

You can enter multiple categories IDs and custom intervals (such as 1h, 30 min, 3 months, etc)

image
image728×822 20.9 KB

Note: I kept the post_action_type_id 6, which I believe is related to a user receiving a warning; I’m not sure if you want to count as well; feel free to remove it then.