So its legit that you are not being notified for that.
The sharing badge may need a tweak, its grabbing min(id) instead of max which means it is unlikely to hit the threshold
SELECT views.user_id, i2.post_id, i2.created_at granted_at
FROM
(
SELECT i.user_id, MIN(i.id) i_id
FROM incoming_links i
JOIN badge_posts p on p.id = i.post_id
WHERE i.user_id IS NOT NULL
GROUP BY i.user_id,i.post_id
HAVING COUNT(*) > 25
) as views
JOIN incoming_links i2 ON i2.id = views.i_id
One possibility would be to add another return column to the badge queries, call it repeat_id. Could be trivially mapped to the seq column in the badge_users table. Then have repeat_id * TIMESPAN('1 year') somewhere in the query.
my concern is around the 1 post per year req check, for each user you need to break it down to an artificial set with year-start / year-end and then join to that. it can get to be an expensive query.
That’s probably because they’re bronze badges, and they got backdated past the notification cutoff date… we should either nix that notification cutoff or fix the badges