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 HAVING COUNT(*) >= 5 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?

For posterity: after a direct look, @JammyDodger pointed out that Query targets posts had somehow become unchecked. That seems to have done it. He forced the job to run & :drum: … two silvers were granted! :tada: I’m setting this up on my production site & the preview tells me I should see 12 silvers tomorrow. :tada:

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 ( SELECT user_id, granted_at, 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 ( SELECT user_id, granted_at, 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:

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. [Selection_1418]

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.

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. [02c298ac17213cbd4108be9f876f27493a8d0e61] 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 ( SELECT user_id, id, created_at, 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)) [image]

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:

[image] JammyDodger: 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: 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?

[image] JammyDodger: it seems that the auto badge granter will only award multiple badges if they’re based on specific posts So… even though the bronze badges are awarded based on posts… that doesn’t count, does it? [image] JammyDodger: 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? 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 ( SELECT user_id, granted_at, post_id, 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: [test_eight - first run] I then awarded them 5 more Badge A for 5 more different posts and ran it again: :tada: [image]

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:

[image] G Ann Campbell: (not that I don’t trust you :joy:) Oh for sure. Trust, but verify is definitely a smart choice. :slight_smile: [image] G Ann Campbell: And… is this a good time to ask that the tutorial be updated with the things you’ve learned in this quest? :smiley: I shall see if I can add a little something something. :slight_smile: :+1:

A silver badge for _each_ 5 bronze badges

Community Building Data & reporting

ganncamp (G Ann Campbell) November 10, 2023, 12:44pm 24

13! 13 silvers awarded overnight on our production instance!

Thanks for all your patient help @JammyDodger!

Topic		Replies	Views
Assigned a badge based on multiple badge assignment Data & reporting sql-triggered-badge	8	1234	March 18, 2022
Awarding badges multiple times with custom SQL Data & reporting sql-triggered-badge	2	1273	October 25, 2017
Grant a badge based on number of bronze/silver/gold badges Data & reporting sql-triggered-badge	0	326	January 17, 2024
SQL Querying in Badges Data & reporting sql-triggered-badge	1	1120	December 4, 2015
An extra badge based on already granted badges number? Data & reporting sql-triggered-badge	1	739	May 23, 2023

A silver badge for _each_ 5 bronze badges

Related topics