Quiero obtener la lista de usuarios que regresaron a la comunidad después de un período de 6 meses (last_seen_at).
Sé que podemos obtener la fecha de last_seen_at de la tabla users. Puedo comparar esta marca de tiempo y verificar si es mayor a 6 meses y obtener los usuarios que no han visitado en los últimos 6 meses, pero una vez que alguien visita la comunidad, esta marca de tiempo se sobrescribe con la fecha y hora actual, lo que dificulta el seguimiento del usuario.
¿Es posible verificar si alguien regresó al foro después de un intervalo de tiempo desde su última visita?
Los detalles que necesita se encuentran en la tabla user_visits. La parte complicada para mí es restar el valor de la visita anterior de cada entrada de visita. Parece requerir una función de ventana. Basándome en la respuesta a esta pregunta en StackExchange, parece que la función LEAD funcionará para esto.
Intenta con esta consulta y ve si devuelve los datos que buscas. Por defecto, devuelve todas las visitas de usuario que ocurrieron después de un período de 180 días entre visitas. Ese valor se puede ajustar estableciendo el parámetro days_away de la consulta.
--[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
Avísame si notas algún problema con los datos que se devuelven.
Disculpa que te moleste de nuevo, ¿sería posible obtener el username en lugar del user_id de la consulta SQL anterior? En la página del explorador de datos, el resultado de la consulta muestra el nombre de usuario, pero cuando exporto los resultados como un archivo CSV, los nombres de usuario son reemplazados por su user_id. Prefiero tener también los nombres en el archivo CSV.
--[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
Avísame si obtienes algún error de tiempo de espera al ejecutarlo. Ten en cuenta que todavía he incluido el ID del usuario en los resultados que se devuelven. Esa columna ahora está etiquetada como id. Si no quieres que se devuelva el ID, elimina la línea dbv.user_id AS id, de la declaración SELECT final.
