What cool badge queries have you come up with?

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

There are lots of potential badges that we can come up with using SQL queries. I thought I’d start a topic for people to share some of the queries and badges they’ve created. I for one am looking forward to seeing some cool inspiration!

:information_source: This post is a wiki topic. If you have a good badge query to add, please reply to this topic with your query, and then edit this post (pencil icon in the upper right) and include a link to your reply here in the following index. Thanks!

Badges for…

More Ideas : Some common badge queries


What would be a good badge query for awarding the member(s) who end up being the “Top Referrers” of the week / month?

Also, one for having the “Top Referred Topic(s)” of the week / month?

1 Like

I’d like to grant badge to user which has read number of pre-selected topics (“recommended reading”). Is it safe (also performance-wise) to use table topic_views for this badge?


Poster of the month Badge

Variation of earlier mentioned top poster, this time awarded to one member each month:

SELECT DISTINCT ON (period) user_id, CONCAT(EXTRACT (YEAR FROM created_at), EXTRACT(MONTH FROM created_at)) AS period, COUNT(id) AS counter, MAX(created_at) AS granted_at
FROM badge_posts
WHERE created_at < date_trunc(‘month’, NOW())
GROUP BY period, user_id
ORDER BY period, counter DESC

First Upload Badge

SELECT user_id, MAX(created_at) AS granted_at
FROM uploads
GROUP BY user_id
HAVING count(*) > 0


I have this error when this query is running:

    Job exception: ERROR:  duplicate key value violates unique constraint "index_user_badges_on_badge_id_and_user_id_and_seq"
    DETAIL:  Key (badge_id, user_id, seq)=(128, 1, 0) already exists.


process_id 23857
application_version ccf9b7067135f37bde23c80456d21b2d1924f858
current_db default
job            Jobs::BadgeGrant
                messagecode_desc Exception granting badges
                extrabadge_id 128

and this one:

Failed to backfill 'Poster of the Month' badge: 


Too bad… I don’t have any testing platform with that much users and history to check it… As I see it SQL query needs to be limited so that user can’t be awarded “Poster of the Month” more than one time… I’m not sure if adding unique post_id into the query would suffice… Can anybody test it?


I am trying to make a badge that is awarded to all users who were tagged @ in a specific topic id but I’m a bit of a newb so I don’t understand how to go about doing this. :slight_smile:

How about a badge for having completed the new user track with discobot?


Is there any way to define a badge and let the users to give it to each other only limited amount of times?
(something that can be shown up in user cards)

use case is when a user has done a very good thing and another one wants to thanks him/her.

any suggestion?

Here is a tricky one to figure out for our SQL gurus: a badge for being exceptionally relevant. How to measure this? I suggest calculating the ratio of posts with at least one like or bookmark to the total number of posts (by that user).

So far, so good. But there are quite some variables that need tweaking for this to work.

To start with, this only makes sense for users with a certain minimum number of posts. So, let’s say users with less than 10 posts don’t qualify at all. This number might have to be much higher in order to have sufficient variation (i. e. posts that are not liked).

Next: what time period are we looking at? Perhaps this badge doesn’t need a time frame but could be calculated across all posts the user ever made? I’ll leave that open fof the time being.

Then there should probably be a requirement that the users post are spread across several categories or at least several topics. So let’s say, users whose posts live in less than 5 topics are not eligible.

For each eligible user we calculate the relevance ratio. Looking at that distribution, we take the top 33 percent (another figure that probably needs tweaking) and anyone whose ratio is higher than that receives the badge (if it’s a lifetime achievement, then it should be revoked if the conditions no longer apply. If it’s an achievement in a certain time period, it could be awarded multiple times.)

It might also make sense to set the threshold ratio as a fixed value via a site setting.


I keep getting the following error when running these queries. Any help appreciated.

ActiveRecord::PreparedStatementInvalid: missing value for :backfill in /*

  • DataExplorer Query
  • Query: /admin/plugins/explorer?id=3
  • Started by: KingM
  • :xxdummy

Can you post the query that you are trying to run?

DISTINCT ON (p.user_id)
p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
WHERE p.topic_id = 112 AND -- my personal introduction topic
(:backfill OR p.id IN (:post_ids) )

I’ve hence realized that the backfill is just for when the badge query is running and not to use that wile using the data explorer. BUT I tried setting up a badge using the query above and to run the query after a post processed and then had one of my teammates test it and it didn’t give them the badge for posting in the introduction topic.

Alright, so my company wants to focus on the amount of activity individuals spend in each category. Below is the query I came up with. If I set this for the badge query will everyone that fits the qualification get the badge?

select sum(posttotal),userid


((select count(p.id) as posttotal ,p.user_id as userid, t.category_id
from posts p
join topics t on p.topic_id = t.id
where p.user_id > 0
and t.category_id = 10
and t.user_id > 0
and p.deleted_by_id is NULL
and t.deleted_by_id is NULL
group by t.category_id, p.user_id)


(select count(l.id) as posttotal, l.user_id as userid, t.category_id
from post_actions l
join posts p on l.post_id = p.id
join topics t on p.topic_id = t.id
where l.user_id > 0
and p.user_id > 0
and t.user_id > 0
and t.category_id = 10
and l.post_action_type_id = 2
and p.deleted_by_id is NULL
and l.deleted_by_id is NULL
and t.deleted_by_id is NULL
group by t.category_id, l.user_id))
as q1
group by userid

having sum(posttotal) > 10

Here’s a badge query for someone who replies to a post in a category (id=19). The post_number > 1 makes sure that it’s a reply, not the first post.

p.user_id, p.id post_id, p.created_at granted_at
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
WHERE category_id = 19 AND p.post_number > 1
AND (:backfill OR p.id IN (:post_ids) )

Hi, is this still the way to add custom badges? I can’t see a place to add SQL code when I’m creating one. thanks!

badge sql is disabled via site settings out-of-the-box cause often people make very bad sql choices.


Ok, so if i would use some of the SQL ones offered here, would it still be possible? Have you considered adding an easier way to add badges, like a “if this then that” option? if a user gets more than 20 likes in 1 day, award this badge. Just an idea :slight_smile:

If you are self hosted you can re-enable the SQL queries using rails console, see