How to filter out a group?

data & reporting
1

Hello all,

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?:

    SELECT 
    sum(p.score) / count(p) as "average score per post", 
    count(p.id) as post_count, 
    p.user_id
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
  AND u.active
GROUP by user_id, u.views
HAVING count(p.id) > 10
ORDER BY sum(p.score) / count(p) DESC
LIMIT 20
2

The 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!)

1 Like
3

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 (
SELECT user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = 'employees'
)

SELECT
u.id AS user_id
FROM users u
WHERE u.id NOT IN (SELECT * FROM group_users)
ORDER BY user_id
7 Likes