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 ? 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 grou…

[图片] Alessio Fattorini: 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 thi…

I think something like this could do it: -- [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_rece…

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

Just to check, are you looking for Likes and Posts Read all time or are those counts for within the past year as well?

[图片] Alessio Fattorini: 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 a…

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.

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 …

[图片] 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 …

[图片] JammyDodger: us.likes_received, That’s amazing! Thaks so much Jammy

No worries. :slight_smile: 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. :+1: Let me know if anything needs tweaking. :slight_smile:

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 (<…

根据活动创建一些群组的查询

社区数据与报告

alefattorini (Alessio Fattorini) 2023 年9 月 4 日 11:12 9

点赞和帖子阅读数均为所有时间（前者是为了关注好的贡献而不仅仅是帖子，后者是为了平衡）。
帖子最少数量仅限于过去一年，这是一个参数，用于了解成员是否仍然活跃。

这可能是一个好方法，但在我的情况下，我应该大力修改 TL1、TL2 和 TL3，并且需要考虑到以下限制。

抱歉，我不太明白，我应该使用徽章吗？
嗯，我该如何修改上面的查询以将其插入到徽章中？

话题		回复	浏览量
Badge for posts with Likes from a specific group Data & reporting sql-triggered-badge	22	1035	2023 年8 月 27 日
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	1076	2024 年11 月 25 日
Stats for group members Data & reporting sql-query	4	300	2018 年7 月 18 日
Data Explorer query to show progress towards trust level 3 requirements Data & reporting sql-query	1	3057	2020 年6 月 17 日
The default badge queries Data & reporting reference , sql-triggered-badge	1	1064	2023 年8 月 27 日

根据活动创建一些群组的查询

相关话题