Badge for posts with Likes from a specific group

I feel like I was getting close. :upside_down_face: well done. :clap:


Iโ€™m trying to figure out if a CTE is more efficient, but itโ€™s melting my brain :melting_face:

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 IN (SELECT post_id FROM staff_liked)
   AND p.user_id >= 0
GROUP BY p.user_id

3 posts were split to a new topic: What is the 'backfill`?

Hm. That means it wonโ€™t count, for example, our TL0 locked category?

1 Like

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).

1 Like