Quero obter a lista de usuários que retornaram à comunidade após um período de 6 meses (last_seen_at).
Sei que podemos buscar a data last_seen_at da tabela users. Posso comparar este timestamp e verificar se é maior que 6 meses e obter os usuários que não visitaram nos últimos 6 meses, mas assim que alguém visita a comunidade, este timestamp é sobrescrito com a data e hora atuais, dificultando o rastreamento do usuário.
É possível verificar se alguém retornou ao fórum após um intervalo de tempo desde sua última visita?
Os detalhes de que você precisa estão na tabela user_visits. A parte complicada para mim é subtrair o valor da visita anterior de cada entrada de visita. Parece exigir uma função de janela. Com base na resposta a esta pergunta no StackExchange, parece que a função LEAD funcionará para isso.
Tente esta consulta e veja se ela retorna os dados que você procura. Ela retorna por padrão todas as visitas de usuários que ocorreram após um período de 180 dias entre as visitas. Esse valor pode ser ajustado definindo o parâmetro days_away da 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
Me diga se você notar algum problema com os dados retornados.
Desculpe incomodá-lo novamente, seria possível obter o username em vez do user_id da consulta SQL acima? Na página data-explorer, o resultado da consulta mostra o nome de usuário, mas quando exporto os resultados como um arquivo CSV, os nomes de usuário são substituídos por seus user_id. Prefiro ter os nomes no arquivo CSV também.
--[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
Me diga se você receber algum erro de tempo limite ao executá-lo. Observe que ainda incluí o ID do usuário nos resultados retornados. Essa coluna agora está rotulada como id. Se você não quiser que o ID seja retornado, remova a linha dbv.user_id AS id, da instrução SELECT final.
