# 30 days visitor badge

**URL:** <https://meta.discourse.org/t/30-days-visitor-badge/160214>\
**Category:** Support\
**Created:** [August 8, 2020, 6:14pm UTC](https://meta.discourse.org/t/30-days-visitor-badge/160214 "2020-08-08T18:14:35Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![merefield](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/merefield/32/176214_2.png) [@merefield](https://meta.discourse.org/u/merefield)\
**Post date:** [August 9, 2020, 12:09pm UTC](https://meta.discourse.org/t/30-days-visitor-badge/160214/2 "2020-08-09T12:09:14Z")

</div>

FWIW, you can find the existing queries using the [Data Explorer](https://meta.discourse.org/t/32566?silent=true).

e.g: `SELECT * from badges WHERE query LIKE '%10 days%'`

Here’s the one for 365 days:

`WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 365 ) SELECT user_id , "start" + interval '365 days' "granted_at" FROM visits WHERE "rank" = 1`

and 10 days:

`WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 10 ) SELECT user_id , "start" + interval '10 days' "granted_at" FROM visits WHERE "rank" = 1`

so I’m guessing this might do the trick for 30:

`WITH consecutive_visits AS ( SELECT user_id , visited_at , visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s FROM user_visits ), visits AS ( SELECT user_id , MIN(visited_at) "start" , DENSE_RANK() OVER (PARTITION BY user_id ORDER BY s) "rank" FROM consecutive_visits GROUP BY user_id, s HAVING COUNT(*) >= 30 ) SELECT user_id , "start" + interval '30 days' "granted_at" FROM visits WHERE "rank" = 1`

… which I checked runs and seems to come back with apparently sensible results but I’ve not checked the results thoroughly 😅

---

_[View the full topic](https://meta.discourse.org/t/30-days-visitor-badge/160214)._