Get count of recent flags in a particular category

Data & reporting
2

Hi @tpsReports,

I don’t have a very robust Discourse instance available where I can test this out thoroughly, but I think this Data Explorer query might get you going in the right direction. (Someone else can probably chime in to clean it up!)

SELECT count(*)
FROM post_actions AS pac
JOIN posts p on p.id = pac.post_id
JOIN topics t on t.id = p.topic_id
WHERE pac.post_action_type_id in ('3','4','7','8')
AND pac.created_at >= NOW() - INTERVAL '20 MINUTE'
AND t.category_id = '4'

In this example, I’ve specified the time since the flag was created to be 20 minutes (line 6), as that’s what you gave in your example.
In the 7th line, I used the category ID of 4, but you’ll need to use the specific category ID for the category you’re wanting to track.

If you want to be able to refer to the category by name, you could add another join to the query, so it would look like this:

SELECT count(*)
FROM post_actions AS pac
JOIN posts p on p.id = pac.post_id
JOIN topics t on t.id = p.topic_id
JOIN categories c on c.id = t.category_id
WHERE pac.post_action_type_id in ('3','4','7','8')
AND pac.created_at >= NOW() - INTERVAL '20 MINUTE'
AND c.name = 'General'

Then replace where I have used General in line 8 with the name of your category. Keep in mind the category names are case-sensitive.

Please let me know if that helps!

4 Likes