Exclude groups from Top Quality Members Query

Community Building Data & reporting

AI-generated summary

The discussion is about excluding certain groups from a query to find top quality members. outofthebox shared the original query and asked for advice on how to remove specific groups.

pfaffman provided a modified query using not in to exclude groups, but outofthebox reported that it didn’t work as expected since members of excluded groups could still be part of other groups.

pfaffman acknowledged the issue and suggested creating a subquery to get users not in any excluded groups. Meanwhile, RGJ shared an alternative query using NOT IN with a subquery to exclude users who are part of the specified groups. outofthebox expressed gratitude for RGJ’s contribution and support.

1

Hi,

Any advice on how can I remove certain groups from this query?

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 '3 month'
  AND u.active
GROUP by user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100

In advance, thank you!

2

I didn’t really test this.

-- [params]
-- int_list :exclude_groups = 12, 2, 3

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
LEFT JOIN group_users gu on u.id = gu.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
  AND u.active
  AND (gu.group_id not IN (:exclude_groups))
GROUP by p.user_id, u.views
HAVING count(p.id) > 40
ORDER BY sum(p.score) / count(p) DESC
LIMIT 100
3

Thank you @pfaffman. That’s very kind of you.

I ran the script and I see members of the excluded groups appearing in the results. Of course, they are members of many groups. Is there a way for this script to absolutely exclude all members of all groups in the :exclude_groups list?

Thank you for your consideration.

4

Oh. Right. The not in didn’t work as I’d first thought. It mostly does nothing. I think what needs to happen is first a query that gets users not in any groups and then to join that with the other part of the query.

Sorry.

5 

    -- [params]
    -- int_list :exclude_groups = 1,2

    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 '3 month'
      AND u.active
      AND u.id NOT IN(
        SELECT user_id FROM group_users WHERE group_id IN (:exclude_groups)
      )
    GROUP by user_id, u.views
    HAVING count(p.id) > 40
    ORDER BY sum(p.score) / count(p) DESC
    LIMIT 100
6

@RGJ,

Your contributions to the Discourse ecosystem and your amazing support for me as a customer are truly appreciated. I am thrilled that you provide such an incredible hosting service!