# 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:** 13

<div class="post-metadata">

**Author:** ![alefattorini](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/alefattorini/32/119928_2.png) [@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")

</div>

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

```

---

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