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