# User badge counts with badge name filter

**URL:** https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [October 19, 2020, 2:27pm UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098 "2020-10-19T14:27:15Z")
**Posts on this page:** 1
**Showing post:** 3

<div class="post-metadata">

### Author: ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)
#### Post date: [October 23, 2020, 8:34pm UTC](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098/3 "2020-10-23T20:34:41Z")

</div>

Here’s a slightly modified version of that query that adds a `badge name` filter. The `badge name` defaults to `'all badges'`. When set to that value, results for all badges will be returned. If you set the `badge name` to the name of a specific badge, only results for that badge will be returned.

```sql
-- [params]
-- int :posts = 1
-- int :top = 10
-- string :badge_name = all badges

SELECT
username,
COUNT(ub.id) as badge_count
FROM user_badges ub
JOIN users u ON u.id = ub.user_id
JOIN user_stats us
ON us.user_id = ub.user_id
JOIN badges b ON b.id = ub.badge_id
WHERE us.post_count > :posts
AND (u.admin = 'f' AND u.moderator = 'f')
AND CASE
        WHEN 'all badges' = :badge_name
            THEN true
        ELSE b.name = :badge_name
    END
GROUP BY u.username
ORDER BY badge_count DESC

```

---

_[View the full topic](https://meta.discourse.org/t/user-badge-counts-with-badge-name-filter/168098)._