What cool badge queries have you come up with?


(Kane York) #148

Oh… you need to select just a few columns.

SELECT users.id as user_id, current_timestamp as granted_at
FROM users



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:

(Christoph) #150

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

(James Fraser) #151


I am seeking some help to do the following badge

I am trying to encourage users to post in a thread, I want them to post in this particular thread 10 times to earn this badge.

I was looking at

Which works for a single post, I am just unsure how to expand this out to 10 posts minimum.

(Pad Pors) #152

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?

(Christoph) #153

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.

(Nigel Tatschner) #154

Can’t seem to get this to work,
I’ve added;

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 = 172 AND
(:backfill OR p.id IN (:post_ids) )

But running the query isn’t bringing anyone back :’(

(Kane York) #155

Did you change the topic ID? the 172

(Nigel Tatschner) #156

Yeah, I think 172 is the topic number.

(cosmo) #157

where exactly do you go to upload this sql code?? noob here


Go to a badge admin screen e.g. /admin/badges/10

There’s a window in the admin screen like this:

Unless you’re on a site that we host in which case you won’t see it by default.

(Jay Pfaffman) #160

See Badge SQL can no longer be edited by default

(edit: oh. The original post links there too, so perhaps @HAWK’s reply was what you needed)

(Mathias King) #161

Can this be configured to be Category specific instead of Topic specific?

(Jay Pfaffman) #162

Yes. You can issue a badge for whatever SQL you can write that doesn’t crash your server.

(Mathias King) #163

Is there a database diagram anywhere? I know how to write SQL, I just don’t want to query in the dark.

(Jay Pfaffman) #164

This topic and the Data Explorer Plugin are your guides.

(Mathias King) #165

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?

(Mathias King) #167
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) )

(Mathias King) #168

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.