Ich möchte die Liste der Benutzer abrufen, die nach einer Frist von 6 Monaten (last_seen_at) in die Community zurückgekehrt sind.
Ich weiß, dass wir das last_seen_at-Datum aus der users-Tabelle abrufen können. Ich kann diesen Zeitstempel vergleichen und prüfen, ob er größer als 6 Monate ist, und die Benutzer erhalten, die seit den letzten 6 Monaten nicht mehr besucht haben. Sobald jedoch jemand die Community besucht, wird dieser Zeitstempel mit der aktuellen Datum und Uhrzeit überschrieben, was die Verfolgung des Benutzers erschwert.
Ist es möglich zu überprüfen, ob jemand nach einem Zeitintervall seit seinem letzten Besuch zum Forum zurückgekehrt ist?
Die benötigten Details finden Sie in der Tabelle user_visits. Der knifflige Teil für mich ist, den Wert des vorherigen Besuchs von jedem Besucheintrag zu subtrahieren. Es scheint eine Fensterfunktion zu erfordern. Basierend auf der Antwort auf diese Frage auf StackExchange scheint die LEAD-Funktion dafür geeignet zu sein.
Versuchen Sie es mit dieser Abfrage und sehen Sie, ob die Daten zurückgegeben werden, die Sie suchen. Sie gibt standardmäßig alle Besuche zurück, die nach einem Zeitraum von 180 Tagen zwischen den Besuchen stattgefunden haben. Dieser Wert kann durch Festlegen des days_away-Parameters der Abfrage angepasst werden.
--[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
Lassen Sie mich wissen, ob Sie Probleme mit den zurückgegebenen Daten feststellen.
Entschuldigen Sie die erneute Störung. Wäre es möglich, den Benutzernamen anstelle der user_id aus der obigen SQL-Abfrage zu erhalten? Auf der Data-Explorer-Seite zeigt das Abfrageergebnis den Benutzernamen an, aber wenn ich die Ergebnisse als CSV-Datei exportiere, werden die Benutzernamen durch ihre user_id ersetzt. Ich bevorzuge es, die Namen auch in der CSV-Datei zu haben.
--[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
Lassen Sie mich wissen, wenn Sie beim Ausführen Fehler wegen Zeitüberschreitung erhalten. Beachten Sie, dass ich die ID des Benutzers immer noch in die zurückgegebenen Ergebnisse aufgenommen habe. Diese Spalte ist jetzt als id gekennzeichnet. Wenn Sie nicht möchten, dass die ID zurückgegeben wird, entfernen Sie die Zeile dbv.user_id AS id, aus der endgültigen SELECT-Anweisung.
