Мне нужен список пользователей, которые вернулись в сообщество после перерыва в 6 месяцев (поле last_seen_at).
Я знаю, что дату last_seen_at можно получить из таблицы users. Я могу сравнить этот временной штамп и проверить, превышает ли он 6 месяцев, чтобы найти пользователей, которые не заходили за последние 6 месяцев. Однако как только кто-то посещает сообщество, этот штамп перезаписывается текущим временем, что затрудняет отслеживание.
Возможно ли проверить, вернулся ли кто-то на форум после перерыва с момента его последнего визита?
Необходимые данные находятся в таблице user_visits. Сложность для меня заключается в вычитании значения предыдущего визита из каждой записи о визите. Похоже, что для этого требуется оконная функция. Судя по ответу на этот вопрос на StackExchange, функция LEAD должна подойти.
Попробуйте выполнить этот запрос и посмотрите, возвращает ли он нужные вам данные. По умолчанию он возвращает все визиты пользователей, произошедшие после перерыва в 180 дней между визитами. Это значение можно изменить, установив параметр days_away в запросе.
--[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
Дайте знать, если вы заметите какие-либо проблемы с возвращаемыми данными.
Извини, что снова беспокою. Возможно ли получить из приведённого выше SQL-запроса username вместо user_id? На странице data-explorer в результатах запроса отображается имя пользователя, но при экспорте результатов в CSV-файл имена пользователей заменяются на их user_id. Я бы предпочёл видеть в 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
Дайте знать, если при выполнении возникнут ошибки тайм-аута. Обратите внимание, что я всё ещё включил ID пользователя в возвращаемые результаты. Этот столбец теперь имеет метку id. Если вы не хотите, чтобы ID возвращался, удалите строку dbv.user_id AS id, из финального оператора SELECT.
