SQL-задача с подвохом: найдите пользователей, которые вернулись после 30 дней молчания
Представьте, что вы анализируете активность пользователей сервиса. Нужно найти тех, кто вернулся после длительного перерыва.
Есть таблица PostgreSQL:
CREATE TABLE events (
user_id INT,
event_time TIMESTAMP
);
INSERT INTO events VALUES
(1, '2026-10-01 10:00'),
(1, '2026-10-01 12:00'),
(1, '2026-11-01 09:00'),
(1, '2026-11-02 14:00'),
(2, '2026-10-01 10:00'),
(2, '2026-10-31 10:00'),
(3, '2026-09-01 10:00'),
(3, '2026-11-05 10:00'),
(3, '2026-11-05 11:00');
Задача: для каждого пользователя найти все даты возвращения после 30 полных календарных дней без активности.
Ожидаемый результат:
| user_id | return_date |
|---|---|
| 1 | 2026-11-01 |
| 3 | 2026-11-05 |
Условия: несколько событий в один день считаются одной активностью. День последней активности и день возвращения в период молчания не входят. Например, между 1 октября и 31 октября только 29 полных дней без активности, а между 1 октября и 1 ноября уже 30.
Ограничение: решите задачу одним SQL-запросом, без процедур, временных таблиц и циклов.
На первый взгляд достаточно LAG(), но если не учесть дубли событий и границы календарных дней, результат окажется неправильным.
Как бы вы решили эту задачу на PostgreSQL?
3 · 214 · Ветка SQL-задача с подвохом: найдите пользователей, которые вернулись после 30 дней молчания
9 сообщений · –С WITH daily_activity AS ( SELECT DISTINCT user_id, event_time::date AS event_date FROM events ), ordered_events AS ( SELECT user_id, event_date, LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_event_date FROM daily_activity ) SELECT user_id, event_date AS return_date FROM ordered_events WHERE prev_event_date IS NOT NULL AND (event_date - prev_event_date) > 30;👍1С WITH a AS ( SELECT DISTINCT user_id, event_time::date AS act_date FROM events ), P AS ( SELECT user_id, act_date, LAG(act_date) OVER (PARTITION BY user_id ORDER BY act_date) AS prev_date FROM a ) SELECT user_id, act_date AS return_date FROM p WHERE prev_date IS NOT NULL AND act_date - prev_date - 1>= 30 ORDER BY user_id, return_date;