We get quite a few questions about the Aficionado (100 consecutive days) badge. Usually the problem is that a user has missed a day or more due to differences in time zones between their locale and the server’s time.
I’ve always been a little confused by the query that’s used to award the badge. I think I’m understanding it now. Here’s a Data Explorer query that’s using similar logic. It returns the consecutive visit periods for a user. It returns the start date, consecutive days count, and end date for each period of consecutive visits. By default it returns all periods with 10 or more visits. To find what days a user has missed, set the
min_days parameter to 1.
period_end value that’s returned in the first row of results is the current date that you’re running the query on, the user is still in the running for the badge.
-- string :username
-- int :min_days = 10
WITH consecutive_visits AS (
-- The value of s will be the same for each group of consecutive days
visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
WHERE user_id = (SELECT id FROM users WHERE username = :username)
COUNT(*) AS consecutive_days,
GROUP BY user_id, s
HAVING COUNT(*) >= :min_days
ORDER BY period_start DESC