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?
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. Based on the reply to this question 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.
--[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.
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.
--[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.
