Badge for Having Certain Other Badges

Data & reporting
1

I am working on badge SQL that grant’s users a badge when they have all of the badges in a specified list. (E.g. id’s 3, 4 and 5).

I know this badge query works for one badge:

SELECT user_id, count(id) , current_timestamp granted_at
FROM user_badges 
WHERE badge_id=4
GROUP BY user_id

But how would I make this apply to multiple badge id’s? Adding another badge_id = X won’t work because this looks for one badge. Intersect seems like it would work but it isn’t supported. This query would be run daily and wouldn’t target any posts.
Thanks!

3 Likes
2

I use something like this to create Alternative User Level for my members.
You can replace ib.badge_id with the badge id your members should have achieved.

SELECT user_id, CURRENT_TIMESTAMP granted_at, NULL post_id
FROM user_badges pb
WHERE badge_id = 1
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 11
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 124
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 123
)
AND EXISTS (
  SELECT 1 FROM user_badges ib
  WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 127
)
AND EXISTS (
   SELECT 1 FROM user_badges ib
   WHERE pb.user_id = ib.user_id
  AND ib.badge_id = 5
)
7 Likes
3

Thanks for your help, that works perfectly!

1 Like
4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.