# Does Discourse track consecutive days active?

**URL:** <https://meta.discourse.org/t/does-discourse-track-consecutive-days-active/349671>\
**Category:** Data & reporting\
**Tags:** badges, sql-query\
**Created:** [January 30, 2025, 3:46am UTC](https://meta.discourse.org/t/does-discourse-track-consecutive-days-active/349671 "2025-01-30T03:46:54Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)\
**Post date:** [January 30, 2025, 4:23am UTC](https://meta.discourse.org/t/does-discourse-track-consecutive-days-active/349671/3 "2025-01-30T04:23:13Z")

</div>

![Screenshot 2025-01-29 at 8.02.06 PM](https://global.discourse-cdn.com/meta/original/4X/9/4/f/94f4d872101241ba3f1c3c32602c91098aa0a7ba.png)

You can probably make a [data explorer](https://meta.discourse.org/t/32566?silent=true) query. here is the `user_visits` table schema here:

> <https://github.com/discourse/discourse/blob/d75a0b116578d734ffbe0079fe983c33b69099d2/app/models/user_visit.rb#L60-L76C2>

maybe something like this? 🤔

```sql
WITH consecutive_visits AS (
    SELECT
        user_id,
        visited_at,
        visited_at - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visited_at) AS grp
    FROM
        user_visits
),
visit_streaks AS (
    SELECT
        user_id,
        COUNT(*) AS streak_length
    FROM
        consecutive_visits
    GROUP BY
        user_id, grp
)
SELECT
    user_id,
    MAX(streak_length) AS longest_streak
FROM
    visit_streaks
GROUP BY
    user_id
ORDER BY
    longest_streak DESC
LIMIT 100

```

or perhaps you could combine with the `user_actions` table instead (visits with likes and posts?), which is here:

> <https://github.com/discourse/discourse/blob/d75a0b116578d734ffbe0079fe983c33b69099d2/app/models/user_action.rb#L477-L498C2>

---

_[View the full topic](https://meta.discourse.org/t/does-discourse-track-consecutive-days-active/349671)._