# 'Seen in last X days' badge?
**URL:** https://meta.discourse.org/t/seen-in-last-x-days-badge/276735
**Category:** Data & reporting
**Tags:** sql-triggered-badge
**Created:** [4 أغسطس 2016، 8:27ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735 "2016-08-04T08:27:25Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [4 أغسطس 2016، 8:27ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/1 "2016-08-04T08:27:25Z")
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.
---
### Author:  [@JoeZ](https://meta.discourse.org/u/JoeZ)
#### Post date: [4 أغسطس 2016، 11:12م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/2 "2016-08-04T23:12:45Z")
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
```
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [5 أغسطس 2016، 9:03ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/3 "2016-08-05T09:03:02Z")
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.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [5 أغسطس 2016، 3:00م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/4 "2016-08-05T15:00:59Z")
Did you try changing the 7 in the last line to a 1?
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [5 أغسطس 2016، 3:46م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/5 "2016-08-05T15:46:12Z")
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
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [5 أغسطس 2016، 8:35م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/6 "2016-08-05T20:35:26Z")
> [@alefattorini](#):
>
> If I set to 1, I obtain my total users. Correct.
Is that not what you’re looking for? The users who visited at least once in the past 30 days?
> [@alefattorini](#):
>
> If I change to 30 I see only 32 while active users in the last month are 300
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.
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [6 أغسطس 2016، 9:27ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/7 "2016-08-06T09:27:32Z")
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.
---
### Author:  [@JoeZ](https://meta.discourse.org/u/JoeZ)
#### Post date: [6 أغسطس 2016، 11:44ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/8 "2016-08-06T11:44:55Z")
Reason is that above code I posted shows users that have been active every day for the set interval.
---
### Author:  [@JoeZ](https://meta.discourse.org/u/JoeZ)
#### Post date: [6 أغسطس 2016، 11:50ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/9 "2016-08-06T11:50:46Z")
Try something more like this:
```
SELECT
* from users where users.last_seen_at > current_date - interval '30' day
```
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [24 أغسطس 2016، 8:44ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/10 "2016-08-24T08:44:22Z")
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
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [24 أغسطس 2016، 5:14م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/11 "2016-08-24T17:14:56Z")
Change it to run daily.
---
### Author:  [@alefattorini](https://meta.discourse.org/u/alefattorini)
#### Post date: [25 أغسطس 2016، 10:56ص UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/12 "2016-08-25T10:56:12Z")
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?
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [26 أغسطس 2016، 10:56م UTC](https://meta.discourse.org/t/seen-in-last-x-days-badge/276735/13 "2016-08-26T22:56:59Z")
Oh.. you need to select just a few columns.
```plaintext
SELECT users.id as user_id, current_timestamp as granted_at
FROM users
...
```