I am attempting to filter out a group I created named “Employees” from this data explorer tab, what am I doing wrong? How do I exclude group that I have created?:
sum(p.score) / count(p) as "average score per post",
count(p.id) as post_count,
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '6 month'
AND NOT u.admin
AND NOT u.employees
GROUP by user_id, u.views
HAVING count(p.id) > 10
ORDER BY sum(p.score) / count(p) DESC
users table has
primary_group_id (integer) so you could use that in your query if “Employees” is set as primary group for these users. Otherwise I think you’d have to do a separate join w/ the
groups table. (Note — I don’t know the details of table structure off the top of my head but the little search thing on the right when editing a Data Explorer query is super useful for this!)
To get a list of users that excludes the members of a group, you could try something like this. This will exclude the members of the ‘employees’ group. It should be possible to rework your query to use this.
WITH group_users AS (
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = 'employees'
u.id AS user_id
FROM users u
WHERE u.id NOT IN (SELECT * FROM group_users)
ORDER BY user_id