# מצא משתמשים שחזרו לאחר תקופה
**URL:** https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [24 באפריל, 2022, 7:41am UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137 "2022-04-24T07:41:38Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@Senpai](https://meta.discourse.org/u/Senpai)
#### Post date: [24 באפריל, 2022, 7:41am UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/1 "2022-04-24T07:41:38Z")
Hi community,
I want to get the users list who came back on the community after a period of 6 months (last\_seen\_at).
I know we can fetch the `last_seen_at` date from the `users` table. I can compare this timestamp and check if greater than 6 months and get the users who have not visited since the past 6 months but once if someone visits the community this timestamp is overwritten with the current datetime making it difficult to track the user.
Is it possible to check if someone came back to the forum after an interval of time since their last visit?
Thank you!
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [3 במאי, 2022, 10:51pm UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/6 "2022-05-03T22:51:54Z")
> [@Senpai](#):
>
> Is it possible to check if someone came back to the forum after an interval of time since their last visit?
The details you need are in the `user_visits` table. The tricky part for me is subtracting the value of the previous visit from each visit entry. It seems to require a [window function](https://www.postgresql.org/docs/current/functions-window.html). Based on the reply to [this question](https://dba.stackexchange.com/questions/300023/postgresql-subtract-to-each-row-the-previous-row) on StackExchange, it looks like the `LEAD` function will work for this.
Give this query a try and see if it’s returning the data you are looking for. It defaults to returning all user visits that occurred after a period of 180 days between visits. That value can be adjusted by setting the query’s `days_away` parameter.
```sql
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT * FROM days_between_visits WHERE time_away >= :days_away
ORDER BY visited_at DESC
```
Let me know if you are noticing any issues with the data that is returned.
---
### Author:  [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [4 במאי, 2022, 8:55am UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/7 "2022-05-04T08:55:19Z")
Nice. I don’t know if it will fullfill needs of OP, but for me `>x days` is all what I need.
Wonderful, here is all of you who can SQL because I’m too lazy to learn all those tricks 😝
---
### Author:  [@Senpai](https://meta.discourse.org/u/Senpai)
#### Post date: [18 במאי, 2022, 10:06am UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/9 "2022-05-18T10:06:40Z")
Thanks a lot @simon
This works perfect for my use case 💯
You are a true SQL wizard 🧙
---
### Author:  [@Senpai](https://meta.discourse.org/u/Senpai)
#### Post date: [8 ביוני, 2022, 7:03pm UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/10 "2022-06-08T19:03:27Z")
Hi @simon,
Sorry to bother you again, would it be possible to get the `username` instead of the `user_id` from the above SQL query? On the data-explorer page the query result shows the username but when I export the results as a CSV file the usernames are replaced by their user\_id. I prefer to have the names in the CSV file as well.
Thanks
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [8 ביוני, 2022, 7:20pm UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/11 "2022-06-08T19:20:32Z")
> [@Senpai](#):
>
> would it be possible to get the `username` instead of the `user_id` from the above SQL query?
Sure, give this a try:
```sql
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT
username,
dbv.user_id AS id,
visited_at,
time_away
FROM days_between_visits dbv
JOIN users u ON u.id = dbv.user_id
WHERE time_away >= :days_away
ORDER BY visited_at DESC
```
Let me know if you get any timeout errors when running it. Note that I have still included the user’s ID in the results that are returned. That column is now labeled `id`. If you don’t want the ID to be returned, remove the `dbv.user_id AS id,` line from the final SELECT statement.
---
### Author:  [@Senpai](https://meta.discourse.org/u/Senpai)
#### Post date: [8 ביוני, 2022, 10:00pm UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/12 "2022-06-08T22:00:13Z")
Thanks again!
I didn’t get any timeout errors 🙂
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [8 ביולי, 2022, 10:00pm UTC](https://meta.discourse.org/t/get-users-who-returned-after-a-period-of-time/225137/13 "2022-07-08T22:00:46Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.