A silver badge for _each_ 5 bronze badges

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:

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)

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: