Users who like flagged posts

data & reporting
1

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
11 Likes