Checking badge eligibility

Community Building Data & reporting

AI-generated summary

Checking Badge Eligibility

The discussion revolves around checking eligibility for badges, specifically the Aficionado badge for 100 consecutive days of forum activity. Mark_Walkom initially inquires about the best method to verify potential eligibility, using either the Badge admin page or Data Explorer. HAWK confirms that running the query through Data Explorer should return the same results as the admin page.

Simon provides insight into common issues with the Aficionado badge, often related to time zone differences, and shares a Data Explorer query to help identify consecutive visit periods for a user. The query can be used to determine if a user is still eligible for the badge or to find missing days. Mark_Walkom expresses gratitude for the query, stating it has provided the necessary insight.

1

One of our users flagged that they felt they should have a specific badge. I had a poke around the Badges admin page and ran the Preview granted badges query and they aren’t in the list.

Is the best way to check potential eligibility to use something like Data Explorer to get more info from the DB?

2

On the actual badge page itself (via your Admin dashboard) you can preview the query results, which I think is what you’re saying you’ve done.

Running that query through Data Explorer should return the same results.

If you’re still stumped, flick us an email and we’ll help you dig.

3

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.

If the 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.

--[params]
-- string :username
-- int :min_days = 10

WITH consecutive_visits AS (
  SELECT user_id,
       visited_at,
       -- 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
    FROM user_visits
    WHERE user_id = (SELECT id FROM users WHERE username = :username)
)

SELECT
MIN(visited_at) period_start,
COUNT(*) AS consecutive_days,
MAX(visited_at) period_end
FROM consecutive_visits
GROUP BY user_id, s
HAVING COUNT(*) >= :min_days
ORDER BY period_start DESC
4

Perfect, that’s given me the insight I was after :slight_smile: