Some common badge queries

Data & reporting
1

:warning: Enabling Badge SQL entails security and performance risks so it is not available by default.
For more information, see: Enable Badge SQL

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)

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)

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

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

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)

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

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”

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

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

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