Users who don’t have a particular badge

data & reporting
,
1

I’m trying to put together a query that will show users of a group that don’t have a particular badge, like Certified.

Something along the lines of "give me all the users from group VIP where badge Certified does not exist. Thank you.

2 Likes
2

Users who don’t have a particular badge

Well, let’s try this:

WITH exclude_badge AS (
SELECT gu.user_id
FROM badges b, user_badges ub, users u, group_users gu
WHERE u.id = ub.user_id
AND ub.badge_id = b.id
AND u.id = gu.user_id
AND b.name = 'Certified'
AND gu.id = 10 
)

SELECT
u.id AS user_id
FROM users u
WHERE u.id NOT IN (SELECT * FROM exclude_badge)
ORDER BY user_id
LIMIT 10
5 Likes
3

Thanks for your help Sid, I made a couple of tweaks and got it to work with the following:

SELECT gu.user_id
FROM badges b, user_badges ub, users u, group_users gu
WHERE u.id = ub.user_id
AND ub.badge_id = b.id
AND u.id = gu.user_id
AND b.name = 'Certified'
AND gu.group_id = 42
)

SELECT
u.id AS user_id
FROM users u, group_users gu
WHERE u.id = gu.user_id
and gu.group_id = 42
and u.id NOT IN (SELECT * FROM exclude_badge)
ORDER BY user_id
LIMIT 100
4 Likes
4

Members of group who do not have a particular badge

Great! Let’s add params and update my query list :wink:

Final version:
https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/users-from-group-without-badge.sql

5 Likes