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 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.
That is a good question that I don’t immediately know the answer to.
I’m tentatively thinking something using RANK but I may be clutching at straws…
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.
I’ve confused myself. I’m going to have a cup of tea and regroup.
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.
Not everyone has that, Toni You’ll make people jealous. (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 (
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))
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 ).
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.
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.
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:
Then I think this is indeed possible.
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)
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.
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?
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?
magic in my SQL, or does the multiplier happen outside the SQL?
)
→ GPT-4 → SQL Helper.
You’ll make people jealous. 
).
) and hope to put this in production this week! 
