One of our users had 5 “Nice Reply” badges (liked 100 posts and gave 100 likes). Then we moved some topics to a private category, and now he has only 3 badges. It seems the badge query is not counting the likes given or received on the moved topics, perhaps because they moved or perhaps because they are now private.
In debugging this, I copied the badge query to Data Explorer:
select p.user_id, p.id post_id, current_timestamp granted_at from badge_posts p where p.post_number > 1 and p.like_count >= 10
The above query returns only 3 hits for the user, but it should be 5. I noticed it uses the table “badge_posts”. I can’t find the badge_posts table in the Data Explorer tree view. So I changed “badge_posts” to “posts”, and then it returns 5 as expected.
Where does the “badge_posts” table come from, and shouldn’t the badge query use the “posts” table instead?