'Seen in last X days' badge?

Data & reporting
1

What about a badge like “Seen here last 30 days” or “Seen here last 60 days”
Clearly, you can lose it in case you haven’t visited discourse recently.

1 Like
2

I’m not familiar with revocation queries, but here is how it is assigned. Last line is how many days

SELECT username, user_id, current_timestamp granted_at, max(days) from (
    WITH StartingPoints AS (
        SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
        FROM user_visits AS A
        WHERE NOT EXISTS (
            SELECT *
            FROM user_visits AS B
            WHERE B.visited_at = A.visited_at - 1 AND
            B.user_id = A.user_id
        )
    ), 
    EndingPoints AS (
        SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
        FROM user_visits AS A
        WHERE NOT EXISTS (
            SELECT *
            FROM user_visits AS B
            WHERE B.visited_at = A.visited_at + 1 AND
            B.user_id = A.user_id
        )
    )
    SELECT u.username, S.user_id AS user_id, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at +1) AS Days
    FROM StartingPoints AS S
    JOIN EndingPoints AS E ON E.rownum = S.rownum
    JOIN users u ON u.id=S.user_id
) x
GROUP BY x.username, x.user_id 
HAVING max(days)>=7
3

Sorry, maybe there was a misunderstanding… I meant at least 1 visit in the last 30 days. Not at least a visit each day, in the last 30 days. Results are not matching “Active Users” in the about panel.

4

Did you try changing the 7 in the last line to a 1?

5

If I set to 1, I obtain my total users. Correct.
If I change to 30 I see only 32 while active users in the last month are 300

6

Is that not what you’re looking for? The users who visited at least once in the past 30 days?

That’s because only 32 of the 300 visited the site each of the past 30 days. I’m not quite sure what Discoure’s definition of “Active” is.

7

My total users are 1.1k and active users (logged in) last month are around 300
Why does the badge show just 32? Setting 30 days?
Looks odd.

1 Like
8

Reason is that above code I posted shows users that have been active every day for the set interval.

1 Like
9

Try something more like this:

SELECT
* from users where users.last_seen_at > current_date - interval '30' day
1 Like
10

it doesn’t work, I need people that have been active at least one day for a set interval

Contract violation:
Query does not return a 'user_id' column

@riking @mcwumbly

11

Change it to run daily.

12

I already did it, I need a badge for who have been active at least one day in the last 30 days
and a badge for how have posted at least once in the last 30 days
Can you help me?

Pasted image401×622 24.7 KB

13

Oh… you need to select just a few columns.

SELECT users.id as user_id, current_timestamp as granted_at
FROM users
...
1 Like