Has anyone come up with some SQL to display a list of users (top 10 maybe), ordered by the total number of Badges they have?
I’ve had a poke around in the Data Explorer and looked at the “user_badges” table and can see there is a “user_id” column and “badge_id” column but I’m not skilled enough in SQL to be able to run some kind of count or join query to produce a top ten list.
Has anyone done something like this already?
Test this:
-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
Hi @SidV 
This works quite well, thanks!
However it’s not quite accurate…
Here’s what I get:
Yet hovering over a few of those users shows different numbers.
Memento has 44 (not 48):
Brian has 33 (not 35):
Yet Paul does actually have 34!
If I remove the line that’s excluding admins, it goes a little haywire:
Ping has 52 (not 179):
But Ozone does indeed have 47:
Any idea what might be causing these inaccuracies @SidV ?
Thanks once again for your help on this one 
Yes. A lot of badges have the option to win more that one time. 
Check this if it more accurate:
-- [params]
-- int :posts = 100
-- int :top = 10
SELECT u.username, count(ub.id) as "Badges"
FROM user_badges ub, users u, user_stats us, badges b
WHERE u.id = ub.user_id
AND u.id = us.user_id
AND b.id = ub.badge_id
AND us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND b.multiple_grant = 'f'
GROUP BY u.username
ORDER BY count(ub.id) desc
LIMIT :top
D’oh! 
Yes, of course 
However, your updated reply is actually exactly what I need 
Thanks ever so much @SidV 
