A silver badge for _each_ 5 bronze badges

We’ve created a bronze badge, Users Helping Users, for people trying to help other users. That’s granted manually (in order to tell the difference between attempts at being helpful and "me too"s).

For each 5 bronze badges, we want to award a sliver, Invaluable, badge.

The SQL I’ve come up with finds the right two users, but only once each:

SELECT user_id, current_timestamp AS granted_at 
FROM user_badges
WHERE badge_id = 110 -- Users Helping Users
    AND (:backfill OR user_id IN (:user_ids))
GROUP BY user_id

Am I missing some :magic_wand: magic in my SQL, or does the multiplier happen outside the SQL?

Also, it’s really not clear to me how the math happens so that my two users don’t get new badges every day (this will run on a nightly trigger) even if they haven’t gotten 5 new bronze badges.

Is there a guide to this that my searching didn’t find?

Okay, deeper searching led me to a trail that landed me at the guide

The backfill thing is still a bit muddy, but I’ve decided to (duh!) trial this on our test instance. I suppose backfill runs overnight, so I’ll know more tomorrow.

Unless you tick ‘Can be granted multiple times’ then it shouldn’t be awarded more than once, even if they qualify for it a second (or more) time. :+1:

Okay, I’ve re-read your post more thoroughly and I think I have a clearer idea of what you’re aiming for.

Based on the current SQL those users will only ever be awarded the Silver badge once, even if you allow it to be granted multiple times:

  • I’ve created the relevant badges on my test site (including allowing multiple)
  • Granted 5 of the Bronze and run the background job to grant the badge (Silver badge granted successfully :partying_face:)
  • I then ran the BadgeGrant job again and they were not granted the Silver a second time
  • I then pumped up their Bronze badges to 11 and ran the badge grant job again
  • No second Silver badge was awarded

I did check ‘multiple times’

but it was only granted once, even though it should have been twice

1 Like

So… what do I have to do to get my user with 12 bronze badges awarded the 2 silvers he deserves?

1 Like

That is a good question that I don’t immediately know the answer to. :slight_smile:

I’m tentatively thinking something using RANK but I may be clutching at straws… :thinking:

Rough prototype...
WITH badge_count AS (

        RANK() OVER (PARTITION BY user_id ORDER BY granted_at ASC) AS rank
    FROM user_badges
    WHERE badge_id = 110


SELECT user_id, granted_at
FROM badge_count
WHERE rank IN (5,10,15,20,25,30,35,40,45,50)

Okay, a second attempt using ROW_NUMBER instead:

WITH badge_count AS (

        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY granted_at ASC) AS row
    FROM user_badges
    WHERE badge_id = 110


SELECT user_id, granted_at
FROM badge_count
WHERE row % 5 = 0

Though, on further testing, this works correctly in the preview but isn’t being granted multiple times when the actual badge grant job runs. I’m not really sure why though. :thinking:

I’ve confused myself. I’m going to have a cup of tea and regroup. :slight_smile:

1 Like

Well… I gave my user another 4 bronzes yesterday to see if the overnight run would at least add the new badge he’d just “earned”. (I figured if I had to catch up the rest of them by hand, I could.) But even that didn’t work.

I can’t help you with the query, but suggest you ask the Discourse AI bot for help - select the bot icon in the top
image → GPT-4 → SQL Helper.

I’ve found it very good in creating data explorer queries from just telling it what I need and guess it can help with badge queries.

1 Like

Not everyone has that, Toni :shushing_face: You’ll make people jealous. :slight_smile: (though @ganncamp does, so it is an option)

But… I’m pretty sure my query is sound. It picks out the right ones in the Preview, but just doesn’t award more than one when using the ‘Update Daily’ trigger.


I’ve set up another one almost the same to test it out based on a badge for ‘every 5 posts in a specific topic’ using the ‘when a user creates or edits a post’ trigger - and that one works perfectly. I am making enquiries as to what the difference may be…

Here’s the SQL for that test badge to compare if anyone can spot anything:

WITH post_count AS (

        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at ASC) AS row
    FROM posts
    WHERE topic_id = 864


SELECT user_id, created_at granted_at, id post_id
FROM post_count
WHERE row % 5 = 0
  AND (:backfill OR id IN (:post_ids))



I have access to it @tpetrov but my attempts to use it have been… less than fruitful. Or maybe I just excel at asking hard questions? :laughing:

1 Like

After a little exploration and consultation, it seems that the auto badge granter will only award multiple badges if they’re based on specific posts. So these type of ones will only ever award the first badge (the Preview is misleading :frowning:).

I think in similar cases ‘escalating’ badges can work well (like the Solved ones). So a Silver for 30 and a Gold for 100, for instance, if that could be a viable alternative?

So… even though the bronze badges are awarded based on posts… that doesn’t count, does it?

I don’t know. I don’t think I understand the question. :laughing:

I guess the suggestion is not to find 5 badges, but 5 posts where a badge has been awarded? I can do that. I’ve more or less already done it in my “find new posts to award badges for” report.

Hmmm. :thinking: I think I see where you’re going with this. Let me have another go…

@ganncamp - I think we may have some success… :slight_smile:

Based on Bronze Badge A being awarded through the post wrench or by giving a reason on the /admin/users/{user_id}/{username}/badges page:

post wrench /admin/users/user_id/username/badges

Then I think this is indeed possible. :partying_face:

WITH badge_count AS (

        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY granted_at DESC) AS row
    FROM user_badges
    WHERE badge_id = 110
      AND post_id IS NOT NULL

SELECT user_id, granted_at, post_id
FROM badge_count
WHERE row % 5 = 0
  AND (:backfill OR post_id IN (:post_ids))

(Adding in the AND post_id IS NOT NULL protects it from if someone is awarded one without a reason, otherwise it breaks)

other badge settings

I have just tried it out and fast-forwarded by triggering the GrantBadge background job and my test user has finally received the full credit they deserve. :slight_smile:

I then awarded them 5 more Badge A for 5 more different posts and ran it again: :tada:


Thanks @JammyDodger! :tada: :tada: :tada:

I’ve set this up on my test instance (not that I don’t trust you :joy:) and hope to put this in production this week! :star_struck:

And… is this a good time to ask that the tutorial be updated with the things you’ve learned in this quest? :smiley:

Oh for sure. Trust, but verify is definitely a smart choice. :slight_smile:

I shall see if I can add a little something something. :slight_smile: :+1:

1 Like

Well… when the job ran overnight,
my user with 6 bronzes got 1 silver :white_check_mark:
my user with 13 bronzes got… 1 silver :slightly_frowning_face:

This part in the tutorial made me think a separate, explicit backfill job wasn’t necessary

Since daily a full backfill runs regardless you must take that into consideration and include handling of the :backfill parameter.

For users who’ve already earned more than 1 silver, how do I get those awarded? Do I have to do that manually?

The backfill is the daily job. The ‘Update Daily’ trigger is essentially just that, whereas the other triggers are much more ‘at the time’ (eg. if a badge used ‘when a user creates or edits a post’ that wouldn’t need to wait overnight to get it awarded).

Can you pop a screenshot of your badge in so I can see what may be different?

Here you go:

BTW, some of these things I checked because… I don’t know what I’m doing :joy:

1 Like

And your user with 13 ‘Badge A’ has the reasons filled in when you look at their /admin/users/{user_id}/{username}/badges page?

You’ve got the important bits the same. :slight_smile: The others ones are still legitimate, but optional.

Are you running this on your staging site or on a self-hosted test site?

Woah! Mystery solved!

I had assumed the job ran in the wee hours of (my) morning.

In fact, I had originally granted this user badges without reasons, so yesterday I revoked them all, dug up posts from him and re-granted. The job ran in the middle of my granting.

This is on my staging site, BTW.

I suppose the 2nd badge will be granted in a few hours, but I would like to see multiple badges granted at once. Will that happen if I revoke the silver? Will he be granted 2 new silvers in… 2h?

1 Like