Voglio ottenere l’elenco degli utenti che sono tornati nella community dopo un periodo di 6 mesi (last_seen_at).
So che possiamo recuperare la data last_seen_at dalla tabella users. Posso confrontare questo timestamp e verificare se è maggiore di 6 mesi e ottenere gli utenti che non hanno visitato negli ultimi 6 mesi, ma una volta che qualcuno visita la community, questo timestamp viene sovrascritto con l’ora e la data correnti, rendendo difficile il tracciamento dell’utente.
È possibile verificare se qualcuno è tornato nel forum dopo un intervallo di tempo dalla sua ultima visita?
I dettagli di cui hai bisogno sono nella tabella user_visits. La parte complicata per me è sottrarre il valore della visita precedente da ogni voce di visita. Sembra richiedere una funzione finestra. Basandomi sulla risposta a questa domanda su StackExchange, sembra che la funzione LEAD funzionerà per questo.
Prova questa query e vedi se restituisce i dati che stai cercando. Per impostazione predefinita, restituisce tutte le visite degli utenti che si sono verificate dopo un periodo di 180 giorni tra le visite. Questo valore può essere modificato impostando il parametro days_away della query.
--[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
Fammi sapere se noti problemi con i dati restituiti.
Mi dispiace disturbarti di nuovo, sarebbe possibile ottenere lo username invece di user_id dalla query SQL sopra? Nella pagina data-explorer il risultato della query mostra lo username ma quando esporto i risultati come file CSV gli username vengono sostituiti dal loro user_id. Preferirei avere anche i nomi nel file 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
Fammi sapere se riscontri errori di timeout durante l’esecuzione. Nota che ho comunque incluso l’ID dell’utente nei risultati restituiti. Quella colonna è ora etichettata come id. Se non vuoi che l’ID venga restituito, rimuovi la riga dbv.user_id AS id, dall’istruzione SELECT finale.
