Revoking a lower tier badge when a higher one is achieved?

data & reporting
1

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
2

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
3

Thanks, Nathan. I’ll have a look.

1 Like
4

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
5

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
6

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