Je veux obtenir la liste des utilisateurs qui sont revenus sur la communauté après une période de 6 mois (last_seen_at).
Je sais que nous pouvons récupérer la date last_seen_at de la table users. Je peux comparer ce timestamp et vérifier s’il est supérieur à 6 mois et obtenir les utilisateurs qui ne sont pas venus depuis les 6 derniers mois, mais une fois que quelqu’un visite la communauté, ce timestamp est écrasé avec la date et l’heure actuelles, ce qui rend difficile le suivi de l’utilisateur.
Est-il possible de vérifier si quelqu’un est revenu sur le forum après un intervalle de temps depuis sa dernière visite ?
Les détails dont vous avez besoin se trouvent dans la table user_visits. La partie délicate pour moi est de soustraire la valeur de la visite précédente de chaque entrée de visite. Cela semble nécessiter une fonction de fenêtre. D’après la réponse à cette question sur StackExchange, il semble que la fonction LEAD fonctionnera pour cela.
Essayez cette requête et voyez si elle renvoie les données que vous recherchez. Elle renvoie par défaut toutes les visites d’utilisateurs qui se sont produites après une période de 180 jours entre les visites. Cette valeur peut être ajustée en définissant le paramètre days_away de la requête.
--[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
Faites-moi savoir si vous remarquez des problèmes avec les données renvoyées.
Désolé de vous déranger à nouveau, serait-il possible d’obtenir le username au lieu du user_id de la requête SQL ci-dessus ? Sur la page data-explorer, le résultat de la requête affiche le nom d’utilisateur, mais lorsque j’exporte les résultats dans un fichier CSV, les noms d’utilisateur sont remplacés par leur user_id. Je préférerais avoir les noms dans le fichier CSV également.
--[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
Faites-moi savoir si vous rencontrez des erreurs de délai d’attente lors de son exécution. Notez que j’ai toujours inclus l’ID de l’utilisateur dans les résultats retournés. Cette colonne est maintenant étiquetée id. Si vous ne souhaitez pas que l’ID soit retourné, supprimez la ligne dbv.user_id AS id, de l’instruction SELECT finale.
