It's not a sophisticated query, it only checks the last X days from 'today' and checks to see if you've been reading posts for each day. The 31% badge query, for example:
SELECT user_id, 0 post_id, current_timestamp granted_at, count(user_id)
FROM user_visits
WHERE date_trunc('day',user_visits.visited_at) >=
date_trunc('day', now() - interval '31 day') AND
(:backfill OR user_id IN (
SELECT trigger_post.user_id
FROM posts trigger_post
WHERE trigger_post.id IN (:post_ids) )
)
GROUP BY user_id
HAVING count(user_id) >= 31
Or without all the cruft that's required so that DC will accept it:
SELECT user_id, count(user_id)
FROM user_visits
WHERE date_trunc('day',user_visits.visited_at) >=
date_trunc('day', now() - interval '31 day')
GROUP BY user_id
HAVING count(user_id) >= 31
There is no 'checking over the past 100 days.'
