Find users with highest number of flags

Hello everyone :grinning:

I’m not really an expert at queries. What I need is a list of usernames of the users with the most (highest number) of flagged posts (flags that have been approved by a moderator) and display this information in order much like the example below:

Username Flag Count
Discourse_test 12
Testing 9
Bob 4
Aaron15 1

If anyone can help I would be thankful.

2 Likes

Not an expert, possibly:

SELECT u.username "Username", COUNT(pa.agreed_at) "Flag Count"
FROM post_actions pa
JOIN posts p ON pa.post_id = p.id
JOIN users u ON p.user_id = u.id
WHERE pa.agreed_at IS NOT NULL
GROUP BY u.username
ORDER BY "Flag Count" DESC
9 Likes

I’ll try that. Thank you @Arkshine :star_struck:

Upon trying this it works very well. The flags count not being in order isn’t the biggest issue. I can live without it. Thanks again!

I’ve updated above the SQL.

2 Likes