# 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:** 13
**Page:** 1
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [August 30, 2023, 9:50am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/1 "2023-08-30T09:50:27Z")
In my community I need to segment my people based on:
- likes received (30 - 100 - 200 )
- posts read 1k 2k 5k
- minimum of post over last year
How can I do this using the [data explorer](https://meta.discourse.org/t/32566?silent=true)?
I’d like to have a query where I put those parameters and it lists the people, so I can add them manually to a group. Very easy
Some hint? Where can I start from?
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [August 31, 2023, 3:53pm UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/2 "2023-08-31T15:53:48Z")
I think something like this could do it:
```sql
-- [params]
-- int :likes_received
-- int :posts_read
SELECT
us.user_id,
us.likes_received,
us.posts_read_count
FROM user_stats us
JOIN users u on u.id = us.user_id
WHERE u.last_posted_at > CURRENT_DATE - INTERVAL '1 YEAR'
AND us.likes_received >= :likes_received
AND us.posts_read_count >= :posts_read
ORDER BY 2 DESC, 3 DESC
```
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [September 1, 2023, 12:39pm UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/4 "2023-09-01T12:39:23Z")
This is great!
How can find if the post at least 10 times over the last year?
Not just one like in your query
How can integrate this query? [Posts created for period](https://meta.discourse.org/t/posts-created-for-period/275138)
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 3, 2023, 11:10am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/5 "2023-09-03T11:10:49Z")
Just to check, are you looking for Likes and Posts Read _all time_ or are those counts for within the past year as well?
---
### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [September 3, 2023, 11:35am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/6 "2023-09-03T11:35:21Z")
> [@alefattorini](#):
>
> - likes received (30 - 100 - 200 )
> - posts read 1k 2k 5k
> - minimum of post over last year
These look suspiciously like existing Trust Level groups (and the population of those is automated by similar measures) - why don’t you just amend the existing thresholds and have it all done for you?
`/admin/site_settings/category/trust`
e.g. for TL2 (members are in `trust_level_2` or equivalent in your dialect):
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [September 3, 2023, 11:38am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/7 "2023-09-03T11:38:55Z")
The automation script will now add people to a group of they get a badge. If you can use custom sql for badges then you can automate it, but it does sound like trust levels.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 3, 2023, 11:48am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/8 "2023-09-03T11:48:28Z")
I can see the advantages of making custom ones. For instance, only TL3 relies on minimum engagement over time. So something like this could also drop people from each custom group if their engagement drops over the year.
They also wouldn’t be tied to the stock abilities and could take advantage of group-enabled features or specific premium categories.
I don’t know what the specific set up is for these though, so it may be achievable through trust levels.
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [September 4, 2023, 11:12am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/9 "2023-09-04T11:12:44Z")
> [@JammyDodger](#):
>
> Just to check, are you looking for Likes and Posts Read _all time_ or are those counts for within the past year as well?
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.
> [@merefield](#):
>
> These look suspiciously like existing Trust Level groups (and the population of those is automated by similar measures) - why don’t you just amend the existing thresholds and have it all done for you?
It could be a good way but in my case I should heavily modify TL1 TL2 and TL3 and need to keep into account the limitations below
> [@JammyDodger](#):
>
> I can see the advantages of making custom ones. For instance, only TL3 relies on minimum engagement over time. So something like this could also drop people from each custom group if their engagement drops over the year.
>
> They also wouldn’t be tied to the stock abilities and could take advantage of group-enabled features or specific premium categories.
> [@pfaffman](#):
>
> The automation script will now add people to a group if they get a badge. If you can use custom sql for badges then you can automate it, but it does sound like trust levels
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?
---
### Author:  [@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")
> [@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.
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [September 5, 2023, 11:02am UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/11 "2023-09-05T11:02:34Z")
> [@JammyDodger](#):
>
> `us.likes_received,`
That’s amazing! Thaks so much Jammy
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 5, 2023, 12:35pm UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/12 "2023-09-05T12:35:24Z")
No worries. 🙂 Hopefully with the first one you can check you’re getting the results you’re expecting, and the second should make adding them to a group easier. 👍
Let me know if anything needs tweaking. 🙂
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [September 5, 2023, 12:49pm UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/13 "2023-09-05T12:49:41Z")
I merged and improved them (with my poor sql skills), if I need usernames I just download CSV and copy/paste username column
I add likes\_received\_max so I can split groups, excluding the group above.
For example
**first\_steps:** 5 likes (\<30), 500 posts read, \>5 post last year,
**beginners:** 30 likes (\<100), 1000 posts read, \>10 post last year
**padawan:** 100 likes, 2000 post read, \>10 post last year
**hero:** 200likes, 5000 post read, \>10 post last year
```plaintext
-- [params]
-- int :likes_received
-- int :posts_read
-- int :likes_received_max
-- int :posts_count
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,
u.username,
us.likes_received,
us.posts_read_count,
ua.posts_count,
u.title
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 >= :posts_count
AND us.likes_received < :likes_received_max
ORDER BY 2 ASC, 3 ASC, 4 ASC
```
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [October 5, 2023, 12:50pm UTC](https://meta.discourse.org/t/query-to-create-some-groups-based-on-activity/277156/14 "2023-10-05T12:50:03Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.