# Badge for posts with Likes from a specific group
**URL:** https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [14 באוגוסט, 2015, 8:44pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728 "2015-08-14T20:44:45Z")
**Posts on this page:** 5
**Page:** 2
### Author:  [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [26 באוגוסט, 2023, 9:36pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/26 "2023-08-26T21:36:56Z")
I feel like I was getting close. 🙃 well done. 👏
> [@Badge for posts with Likes from a specific group](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/6):
>
> Do you mean you want the same query for all categories? SELECT p.user\_id, min(p.created\_at) granted\_at, MIN(p.id) post\_id FROM badge\_posts p JOIN topics t ON t.id = p.topic\_id WHERE ( SELECT count(\*) FROM post\_actions pa WHERE pa.post\_id = p.id AND post\_action\_type\_id = ( SELECT id FROM post\_action\_types WHERE name\_key = 'like' ) AND pa.user\_id IN ( SELECT gu.user\_id FROM group\_users gu …
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [26 באוגוסט, 2023, 9:47pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/27 "2023-08-26T21:47:09Z")
I’m trying to figure out if a CTE is more efficient, but it’s melting my brain 🫠
```sql
WITH staff_liked AS (
SELECT COUNT(*), pa.post_id
FROM post_actions pa
JOIN group_users gu ON gu.user_id = pa.user_id
WHERE post_action_type_id = 2
AND gu.group_id = 3
AND deleted_at IS NULL
GROUP BY pa.post_id
HAVING COUNT(*) >= 5
)
SELECT p.user_id, MAX(p.created_at) granted_at
FROM badge_posts p
WHERE p.id IN (SELECT post_id FROM staff_liked)
AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
```
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [26 באוגוסט, 2023, 10:00pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/28 "2023-08-26T22:00:33Z")
3 posts were split to a new topic: [What is the 'backfill`?](https://meta.discourse.org/t/what-is-the-backfill/276788)
---
### Author:  [@Firepup650](https://meta.discourse.org/u/Firepup650)
#### Post date: [26 באוגוסט, 2023, 11:40pm UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/29 "2023-08-26T23:40:18Z")
> [@JammyDodger](#):
>
> only counts posts from public categories
Hm. That means it won’t count, for example, our TL0 locked category?
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [27 באוגוסט, 2023, 5:09am UTC](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728/30 "2023-08-27T05:09:40Z")
Yeah, the `badge_post` view pre-filters some things out to simplify the queries. You can adjust it to use the `posts` table instead which would include all categories, but it may need an extra line or two to exclude deleted posts, or deleted topics, etc (though this may not be necessary if you want to keep it simple and just let people keep the badge once they’ve earned it, even if their posts are deleted).
[Previous page](https://meta.discourse.org/t/badge-for-posts-with-likes-from-a-specific-group/276728.md?page=1)