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!
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!
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?
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
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.
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.
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.
SELECT
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
from
((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)
union
(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.
SELECT DISTINCT ON (p.user_id)
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) )
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
Enabling Badge SQL entails security and performance risks so it is not available by default.
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!
