# Some common badge queries

**URL:** https://meta.discourse.org/t/some-common-badge-queries/31859
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [August 7, 2015, 7:20am UTC](https://meta.discourse.org/t/some-common-badge-queries/31859 "2015-08-07T07:20:03Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![techAPJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/techapj/32/342990_2.png) [@techAPJ](https://meta.discourse.org/u/techAPJ)
#### Post date: [August 7, 2015, 7:20am UTC](https://meta.discourse.org/t/some-common-badge-queries/31859/1 "2015-08-07T07:20:03Z")

</div>

> ⚠ Enabling Badge SQL entails security and performance risks so it is not available by default.  
> For more information, see: [Enable Badge SQL](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894)

Here are some common badge queries which you can use to create your own awesome badge!

#### Grant a badge if user has created at least one topic in the “foo”\* category

\*(“foo” is the category name, not the category slug)

```sql
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'foo'
) AND p.post_number = 1
GROUP BY p.user_id

```

#### Grant a badge if user has created 5 topics in the “foo”\* category

\*(“foo” is the category name, not the category slug)

```sql
SELECT p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'foo'
) AND p.post_number = 1
GROUP BY p.user_id
HAVING count(*) >= 5

```

#### Grant a badge if user has replied to at least one topic

```sql
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id, p.id
ORDER BY p.user_id, p.id

```

#### Grant a badge if user has replied to 10 topics

```sql
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
GROUP BY p.user_id, p.id
HAVING count(*) >= 10
ORDER BY p.user_id, p.id

```

#### Grant a badge if user has replied to 10 topics in a specific category (add the category ID)

```sql
SELECT
DISTINCT ON (p.user_id)
p.user_id, min(p.created_at) granted_at, MIN(p.id) post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = ... -- [! Add the category ID]
GROUP BY p.user_id
HAVING count(*) > 9

```

If you want to add more categories, use this line instead:

`WHERE t.category_id IN (13, 14, 15, 17, 18, 20, 21) -- [! Use your category ID numbers instead]`

#### Grant a badge if user has created 500 posts

```sql
SELECT user_id, current_timestamp granted_at 
FROM badge_posts  
WHERE (:backfill OR user_id IN (:user_ids) OR 0 NOT IN (:post_ids) )
GROUP BY user_id 
HAVING count(*) >= 500

```

#### Grant a badge to all the members of the group “foobar”

```sql
SELECT user_id, created_at granted_at
FROM group_users
WHERE group_id = (
  SELECT g.id FROM groups g WHERE g.name = 'foobar'
)

```

#### Grant a badge to the first 100 users of the forum

```sql
SELECT id AS user_id, created_at AS granted_at
FROM users WHERE id > 0
ORDER BY created_at ASC LIMIT 100

```

#### Grant a badge for users who have already earned more than `n` badges

```sql
SELECT user_id, current_timestamp granted_at 
FROM user_badges  
GROUP BY user_id 
HAVING count(*) >= ... -- [! Add the number of badges]

```