Badge query for each time posting on a new topic in a category?

data & reporting
8

I think something like this could do it:

WITH book_club_first_posts AS (

SELECT 
    p.topic_id,
    p.user_id,
    MIN(p.id) AS post_id
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE t.category_id = 5  -- replace with the category_id of your book club
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.post_type = 1
    AND p.post_number <> 1
    AND p.user_id > 0
GROUP BY p.topic_id, p.user_id

)

SELECT bcfp.user_id, bcfp.post_id, p.created_at granted_at
FROM book_club_first_posts bcfp
  JOIN posts p ON p.id = bcfp.post_id
WHERE (:backfill OR p.id IN (:post_ids))

badge configuration

1 Like