# Finding 'Top X' users with the most badges
**URL:** https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042
**Category:** Data & reporting
**Tags:** badges, sql-query
**Created:** [December 19, 2018, 4:00pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042 "2018-12-19T16:00:08Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [December 19, 2018, 4:00pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/1 "2018-12-19T16:00:08Z")
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](https://meta.discourse.org/t/32566?silent=true) 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?
---
### Author:  [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [December 19, 2018, 5:14pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/2 "2018-12-19T17:14:30Z")
### Users ordered by badge count
> [@Richie](#):
>
> 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
```
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [December 20, 2018, 8:33pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/3 "2018-12-20T20:33:43Z")
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 😃
---
### Author:  [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [December 21, 2018, 12:02pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/4 "2018-12-21T12:02:26Z")
Yes. A lot of badges have the option to win more that one time. 🤔
Check this if it more accurate:
```sql
-- [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
```
---
### Author:  [@Richie](https://meta.discourse.org/u/Richie)
#### Post date: [December 23, 2018, 2:45pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/5 "2018-12-23T14:45:15Z")
> [@SidV](#):
>
> A lot of badges have the option to win more that one time
D’oh! 🤦🏻♂️
Yes, of course 😊
However, your updated reply is actually _exactly_ what I need 👏🏻
Thanks ever so much @SidV 🙇♂️
---
### Author:  [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [December 23, 2018, 6:06pm UTC](https://meta.discourse.org/t/finding-top-x-users-with-the-most-badges/275042/6 "2018-12-23T18:06:46Z")
I’m glad to help !
**[Query’s list updated](https://github.com/SidVal/discourse-data-explorer/blob/queries/querys.md)** 🚀
Have a great xmas! 🎄