# トップクオリティメンバークエリからグループを除外

**URL:** <https://meta.discourse.org/t/exclude-groups-from-top-quality-members-query/156510>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2020 年 7 月 1 日午後 6:42 UTC](https://meta.discourse.org/t/exclude-groups-from-top-quality-members-query/156510 "2020-07-01T18:42:12Z")\
**Posts on this page:** 1\
**Showing post:** 5

<div class="post-metadata">

**Author:** ![RGJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/rgj/32/523185_2.png) [@RGJ](https://meta.discourse.org/u/RGJ)\
**Post date:** [2020 年 7 月 17 日午前 7:04 UTC](https://meta.discourse.org/t/exclude-groups-from-top-quality-members-query/156510/5 "2020-07-17T07:04:02Z")

</div>

```sql

    -- [パラメータ]
    -- int_list :exclude_groups = 1,2

    SELECT 
        sum(p.score) / count(p) as "投稿あたりの平均スコア", 
        count(p.id) as 投稿数, 
        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

```

---

_[View the full topic](https://meta.discourse.org/t/exclude-groups-from-top-quality-members-query/156510)._