# Query to create some groups based on activity

**URL:** https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [August 30, 2023, 9:50am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156 "2023-08-30T09:50:27Z")
**Posts on this page:** 1
**Showing post:** 10

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 4, 2023, 11:41am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/10 "2023-09-04T11:41:02Z")

</div>

> [@alefattorini](#):
>
> All time for likes and post read (the first is to focus on good contributions not just posts, the second one is to balance it)  
> The minimum of post is only within the past year, it’s a parameter to understand if members are still consistently alive.

In that case, I think something like this could provide the manual look-up:

```sql
-- [params]
-- int :likes_received
-- int :posts_read

WITH user_activity AS (

    SELECT 
        p.user_id, 
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT 
    us.user_id,
    us.likes_received,
    us.posts_read_count,
    ua.posts_count
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 2 DESC, 3 DESC, 4 DESC

```

And tweaking it/stripping it down to just usernames would provide a list you could copy and paste into the ‘Add Users’ box on the group(s) page if you exported the results as a csv (and opened it in something like notepad, for instance):

```sql
-- [params]
-- int :likes_received
-- int :posts_read

WITH user_activity AS (

    SELECT 
        p.user_id, 
        COUNT(p.id) as posts_count
    FROM posts p
    LEFT JOIN topics t ON t.id = p.topic_id
    WHERE p.created_at::date >= CURRENT_DATE - INTERVAL '1 YEAR'
        AND t.deleted_at IS NULL
        AND p.deleted_at IS NULL
        AND t.archetype = 'regular'
    GROUP BY 1
)

SELECT 
    u.username
FROM user_stats us
  JOIN user_activity ua ON UA.user_id = us.user_id
  JOIN users u ON u.id = us.user_id
WHERE us.likes_received >= :likes_received
  AND us.posts_read_count >= :posts_read
  AND ua.posts_count >= 10
ORDER BY 1

```

> [@alefattorini](#):
>
> Sorry I can’t get it, should I use a badge?  
> Uhm how can I modify the query above to insert it into a badge?

This is also possible. 🥳 You would need one badge (and one badge query) for each group, and an accompanying [automation](https://meta.discourse.org/t/discourse-automation/195773) using the 'User Group Membership through Badge` script. You could also automate the badges too rather than granting them manually by enabling the Custom Triggered Badges ([Enable Badge SQL](https://meta.discourse.org/t/badge-sql-can-no-longer-be-edited-by-default/47894) and [Creating triggered custom badge queries](https://meta.discourse.org/t/create-triggered-custom-badge-queries/19336))

There are a lot of moving parts though, so you may want to keep it simple at this stage.

---

_[View the full topic](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156)._