Badge for posts with Likes from a specific group

Data & reporting
26

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

3 Likes
27

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 p.id IN (SELECT post_id FROM staff_liked)
   AND p.user_id >= 0
GROUP BY p.user_id
HAVING COUNT(*) >= 10
2 Likes
28

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

29

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

1 Like
30

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