Badge for Having Certain Other Badges

Community Building Data & reporting

AI-generated summary

The discussion is about creating a badge that grants users another badge when they have a specific set of badges. Qursch is working on a badge SQL query and has a working query for one badge. alefattorini suggests using the EXISTS clause to check for multiple badge IDs, providing an example query that checks for the presence of multiple badges. Qursch confirms that the suggested solution works perfectly.

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!

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
)
3

Thanks for your help, that works perfectly!