Users who like flagged posts
A pattern I’ve spotted on my forum: one or two users habitually “like” posts that break our community guidelines.
These users may not intentionally be trolling, but their “likes” are serving to encourage bad behaviour.
Here’s a query to identify the users who have “liked” the most flagged posts, where the flags have been “agreed” by a moderator:
SELECT likes.user_id, count(*) as count FROM post_actions pa JOIN post_action_types pat ON pa.post_action_type_id = pat.id JOIN post_actions likes ON likes.post_id = pa.post_id AND likes.post_action_type_id = 2 WHERE pat.is_flag AND pat.name_key NOT IN ('notify_user') AND pa.agreed_by_id IS NOT NULL GROUP BY likes.user_id ORDER BY count DESC LIMIT 100