LatornLatorn
Войти

SQL: Последовательные воронки событий — Конверсия и время прохождения воронки

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

Конверсия и время прохождения воронки

Что уже дано

Таблица user_events(event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, event_type TEXT NOT NULL, occurred_at TEXT NOT NULL). До 500 событий; положительные ID, UTC-время YYYY-MM-DD HH:MM:SS в мае 2024. Типы только visit, cart, checkout. При одинаковом времени меньший event_id означает более раннее событие.

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

Посчитайте пользователей последовательной воронки visit → cart → checkout и среднее время её полного прохождения в секундах. Вход пользователя — его первый visit по (occurred_at,event_id). Для следующего шага подходит первое событие нужного типа строго после выбранного предыдущего по той же паре и строго раньше 24 часов от входа. Повторный visit не перезапускает окно. step1_users, step2_users, step3_users — число пользователей, достигших соответствующего шага. avg_seconds_to_checkout — среднее от выбранного visit до выбранного checkout только у завершивших цепочку, с одним наблюдением на пользователя; округление до 2 знаков.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 4 столбцов в указанном порядке и с этими именами: step1_users, step2_users, step3_users, avg_seconds_to_checkout. Счётчики — INTEGER; среднее — REAL либо NULL при отсутствии завершений. Сортировка: Одна агрегатная строка. Даже для пустого журнала верните одну строку: три нуля и NULL. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO user_events VALUES (1,1,'visit','2024-05-01 10:00:00'),(2,1,'cart','2024-05-01 10:10:00'),(3,1,'checkout','2024-05-01 10:20:00'),(4,2,'checkout','2024-05-01 09:00:00'),(5,2,'visit','2024-05-01 10:00:00'),(6,3,'cart','2024-05-01 10:00:00');
Вывод
2|1|1|1200.0
РешениеSQL · SQLite

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

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

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