@sam even though backlog badge assignments run daily, shouldn’t the badge still be assigned at some point? The query proved to work in postgres (identifying user’s who should be receiving the badge) and the preview query has listed the number of badges to be assigned weeks ago yet 0 have been given.
This is the query (let me know if the backfill might be screwing me up):
SELECT topics.id AS topic, posts.id AS post_id, users.id AS user_id, users.title, MAX(posts.like_count) AS like_count, CURRENT_TIMESTAMP granted_at
FROM topics
JOIN posts ON topics.id = posts.topic_id
JOIN users ON users.id = posts.user_id
WHERE posts.like_count > 0
AND users.title = 'Real Estate Agent'
AND (:backfill OR ( users.id IN (:user_ids) ))
GROUP BY topics.id, posts.id, users.id, users.title
HAVING MAX(posts.like_count) >= (SELECT MAX(p.like_count)
FROM topics t
JOIN posts p ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
WHERE t.id = topics.id
GROUP BY u.id order by max desc limit 1)
ORDER BY MAX(posts.like_count) DESC
The settings are set as below:
