What cool badge queries have you come up with?

Hi guys, I managed to test this query up there and it worked! Now, I was wondering if anyone can help me with another challenge. This is the query:

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 'Arts'
) AND p.post_number = 1
and (:backfill OR ( p.id IN (:post_ids) ))
GROUP BY p.user_id
HAVING SUM (views) >= 100

On this example: The user who creates stories in the Arts category and those stories reach 100 views, will receive the badge. The problem is that:
I will need to change this logic over time, because the number of views will increase.
This badge requires 100 views today, but can require 1000 views 6 months from now.

The problem is that when I change the query, it runs again every day and the users who already received it would lose it.

And this would be a disaster, imagine that you reached (now at the beginning of the platform), 10 views and won a Level 2 Badge in Technology. But some months later the platform grows and now Level 2 requires no more 10 views, but 100. You would lose the badge.

There is a way I could prevent this to happen?

1 Like

That query is broken; here is an updated version for getting those on Mailing List Mode:

SELECT user_id, current_timestamp granted_at
FROM user_options
WHERE mailing_list_mode = true
AND (:backfill OR user_id IN (:user_ids))
5 Likes

What does this forum use to create this badge trigger?

That one looks interesting for something I would like to use on my own Discourse forum :slight_smile:

1 Like

This is what we use

SELECT distinct p.user_id, p.created_at granted_at, p.id post_id
FROM badge_posts p
JOIN topics t ON t.id = p.topic_id
JOIN post_actions pa ON pa.post_id = p.id AND 
      post_action_type_id = (
                SELECT id FROM post_action_types WHERE name_key = 'like'
       ) AND 
       pa.user_id IN (
           SELECT gu.user_id
           FROM group_users gu
           WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' ) 
       )
WHERE category_id = (
  SELECT id FROM categories WHERE name ilike 'bug'
) AND p.post_number = 1

Change team to the group you want to use and change bug to the category you want to use it on.

4 Likes

How to exclude rights to a custom badge if a higher tier has already been granted? The objective is to grant only the highest tier a particular user is eligible for…

Let’s say I have 3 badges:

Tier 1: 5 Likes Given, 5 Received
Tier 2: 100 Likes Given, 100 Received
Tier 3: 250 Likes Given, 250 Received

Code below is for Tier 2. Other tiers have nothing different apart from the number ā€˜100’

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received >= 100 
AND us.likes_given >= 100
AND (:backfill OR us.user_id IN (:user_ids))
1 Like

In the /admin/badges/BADGE-ID settings, you simply tick the option for Run revocation query daily. The badge will be removed if they no longer meet the SQL criteria.

image

You might also want to make them Update Daily too so that the granting / revoking happen at around the same time (midnight local time for your instance I think).

3 Likes

Thanks, Nathan. I’ll have a look.

1 Like

Would this also need an extra line in the badge SQL to balance it out? Something like:

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received >= 100 
AND us.likes_given >= 100
AND us.likes_given < 250
AND (:backfill OR us.user_id IN (:user_ids))

(possibly use a BETWEEN or similar, though I haven’t tested that yet :slight_smile:)

And then when the query runs it will give the badge for 100 Likes the first time, ignore it for 101 through 249, and then revoke at 250 (where the next badge will pick up from).


Update: I’ve had a little practice play with BETWEEN, and something like this seems to catch all the right people in the sample test:

SELECT us.user_id
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249 
ORDER BY us.likes_received DESC

So something like this should work if converted into a triggered badge:

SELECT us.user_id, current_timestamp AS granted_at 
FROM user_stats AS us
WHERE us.likes_received BETWEEN 100 AND 249
AND us.likes_given BETWEEN 100 AND 249
AND (:backfill OR us.user_id IN (:user_ids))
3 Likes

This is almost perfect. One rare event may occur: User over-delivers in ā€˜given’ while lagging in ā€˜received’ (or vice-versa), so the badge could be lost before the next is granted (revocation routine).

Looked around and discovered one interesting property: something.badge_id = 123 where ā€˜something’ is the user-defined variable and ā€˜123’ the ID of another badge. I’ll give a try and supplement the original SQLs of Tier 1 and Tier 2 with disqualifying checks against the higher tier badges.

2 Likes

That’s a very interesting point which I hadn’t considered. :slightly_smiling_face: The dual nature does make it a little more prickly. :thinking:

I like the idea of building in a check against the other badges though, that could be useful.

1 Like

I have this query below, which works in the data explorer, but I get the error Sorry, an error has occurred. when trying to use the query to assign badges. Is there something I’m missing here? The query should match if someone is a member of a group called ā€œchapter_ā€ or ā€œmeta_chapter_ā€ and assigned as their primary group.

SELECT distinct on (u.id)
u.id
FROM users u
INNER JOIN groups g
ON u.primary_group_id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
1 Like

For the Custom Triggered ones you need to add an extra line at the start and end. There’s an example in a post of mine above, and you can find more info in Triggered custom badge queries :+1:

3 Likes

Thanks! I think I have to have a look after a good night sleep. SQL has been a while and I guess I have to check some more examples and docs. The thing below is not working, that’s for sure

SELECT ub.id, ub.granted_at, g.name
from user_badges ub
INNER JOIN groups g
ON ub.id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
AND (:backfill OR ub.id IN (:user_ids))

It triggers this error, maybe I’m passing throught the wrong info or naming/column.

ERROR:  column q.user_id does not exist
LINE 11:  JOIN users u on u.id = q.user_id

Edit: ah yes, user_id should be used from somewhere, not id.


And as a final result, these badges are assigned when in a group that starts with the name chapter_ or meta_chapter in them.

SELECT distinct on (gu.user_id)
gu.user_id, gu.updated_at as granted_at
FROM group_users gu
INNER JOIN groups g
ON gu.group_id = g.id
WHERE g.name LIKE 'chapter_%'
OR g.name LIKE 'meta_chapter_%'
AND (:backfill OR gu.id IN (:user_ids))

And this one for if you’re in a group that ends with -team in the name.

SELECT distinct on (gu.user_id)
gu.user_id, gu.updated_at as granted_at
FROM group_users gu
INNER JOIN groups g
ON gu.group_id = g.id
WHERE g.name LIKE '%-team'
AND (:backfill OR gu.id IN (:user_ids))
1 Like

I tried this for users who have created a new Topic in a specific Category that satisfied some conditions.

SELECT DISTINCT ON (p.user_id) p.user_id, p.id post_id, p.created_at granted_at
FROM posts p
INNER JOIN topics t
ON p.topic_id=t.id
WHERE t.category_id = 99 AND p.post_number=1 AND  <conditions here> AND
  (:backfill OR p.id IN (:post_ids) )

ok so my forum is interested in custom ā€œBadges of Shameā€ for users who get flagged the most in a period of X days. what sort of query can i use for this? i don’t think i’ve seen a query that accesses user flag counts in a period.

:wave: Doesn’t answer your question but what does a badge like that aim to achieve? Surely, if a user is getting many flags it would make more sense to follow the moderation guide to deal with them.

My previous thoughts about this

1 Like

i don’t disagree. but humor is also a big part of our forum. i’m not overly disappointed if i don’t find a solution to this (i’m sure it can be done though).

It can definitely be done. I don’t know the exact SQL off the top of my head, but if it’s stored in the database it can be used to grant a custom badge. A friend actually has custom badges based on the number of topics a user has muted.

2 Likes

we don’t actually have a lot of problem users and our forum is quite tame that way. banning and silencing is not a daily event for mods by any means. we have a decent number of users, but i think it’s been a few weeks since someone was disciplined in any way. mostly an older age group. thus flagged posts aren’t exactly common and are generally mild, mostly passive personal attacks or slightly combative.

What about quering the >50 (monthly) leaderboard members? That’s better than selecting overall cheers because it push the permanent contributions.

That badge should enable an specific event in our instance :slight_smile:

2 Likes