LatornLatorn
Войти

SQL: Последовательные воронки событий — Аудит нарушений порядка и первая аномальная подписка

Практическая задача по SQL на тему «Последовательные воронки событий». В условии приведены схема таблиц, ограничения данных, имена столбцов результата и порядок сортировки. Напишите один SELECT или WITH-запрос для SQLite. Перед проверкой продумайте пустой результат, повторяющиеся строки и границы диапазонов, если они встречаются в условии. Проверяется результат запроса, поэтому допустима эквивалентная реализация. Сначала решите задачу самостоятельно; продолжение практики доступно в полном курсе продвинутого SQL.

Аудит нарушений порядка и первая аномальная подписка

Что уже дано

Таблица funnel_log(event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, step_name TEXT NOT NULL, created_at TEXT NOT NULL). До 500 строк; положительные ID, UTC-время YYYY-MM-DD HH:MM:SS в мае 2024. Шаги onboarding, activation, subscription. При равном времени события упорядочены по event_id.

Что нужно сделать

Аномальная subscription не имеет activation того же пользователя строго раньше по (created_at,event_id). Для каждого пользователя хотя бы с одной аномалией верните число таких подписок и идентификатор со временем первой аномальной подписки. Первой считается минимальная пара (created_at,event_id). Поздняя activation не отменяет уже случившуюся аномалию; onboarding не заменяет activation.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 4 столбцов в указанном порядке и с этими именами: user_id, anomalous_subscriptions, first_bad_subscription_id, first_bad_subscription_at. user_id, anomalous_subscriptions, first_bad_subscription_id — INTEGER; first_bad_subscription_at — TEXT; NULL нет. Сортировка: user_id ASC. При отсутствии аномалий результат пустой. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO funnel_log VALUES (1,1,'subscription','2024-05-01 10:00:00'),(2,1,'subscription','2024-05-01 10:10:00'),(3,1,'activation','2024-05-01 11:00:00'),(4,1,'subscription','2024-05-01 12:00:00'),(5,2,'activation','2024-05-01 09:00:00'),(6,2,'subscription','2024-05-01 10:00:00');
Вывод
1|2|1|2024-05-01 10:00:00
РешениеSQL · SQLite

Без регистрации. Гостевая проверка не сохраняет прогресс. Для сохранения перейдите к уроку.

Отправьте решение, чтобы увидеть результаты тестов.

Связанные задачи