Exclude groups from Top Quality Members Query

data & reporting
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!

1 Like
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
4 Likes
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.

2 Likes
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
8 Likes
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!

3 Likes