LatornLatorn
Войти

SQL: RFM-анализ на дату среза — RFM и покупки за последний месяц

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

RFM и покупки за последний месяц

Что уже дано

Таблицы users(user_id INTEGER PRIMARY KEY, registered_at TEXT NOT NULL) и orders(order_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, order_date TEXT NOT NULL, amount INTEGER NOT NULL, status TEXT NOT NULL). До 500 строк в каждой. ID положительные; каждый заказ принадлежит существующему пользователю. Суммы — целые неотрицательные рубли. Статус completed или cancelled. Даты UTC YYYY-MM-DD HH:MM:SS в 2023–2024 годах; пользователи зарегистрированы до среза.

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

Постройте RFM-витрину на 2024-07-01 00:00:00: одна строка на каждого пользователя. Учитывайте completed-заказы до этого момента включительно. recency_days — число полностью прошедших 24-часовых суток после последней подходящей покупки; frequency — количество подходящих заказов; monetary — их сумма. Без таких покупок давность равна NULL, остальные две метрики — 0. Сегменты проверяются по приоритету: no_orders при frequency = 0; vip при frequency >= 3 и monetary >= 10000; active для остальных с recency_days <= 60; churned для остальных. Дополнительно recent_orders_count — число подходящих покупок во включительном интервале от 2024-06-01 00:00:00 до среза. Старые покупки продолжают участвовать в RFM, даже если не входят в этот интервал.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 6 столбцов в указанном порядке и с этими именами: user_id, recency_days, frequency, monetary, segment, recent_orders_count. Все метрики — INTEGER, segment — TEXT; только recency_days может быть NULL. Сортировка: user_id ASC. Пустой users даёт пустой результат. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO users VALUES (1,'2023-01-01 00:00:00'),(2,'2023-01-01 00:00:00'),(3,'2023-01-01 00:00:00'),(4,'2023-01-01 00:00:00'),(5,'2023-01-01 00:00:00');
INSERT INTO orders VALUES (1,1,'2024-06-30 12:00:00',1000,'completed'),(2,1,'2024-05-20 00:00:00',500,'completed'),(3,2,'2024-01-01 00:00:00',4000,'completed'),(4,2,'2024-01-02 00:00:00',3000,'completed'),(5,2,'2024-01-03 00:00:00',3000,'completed'),(6,3,'2024-01-01 00:00:00',100,'completed'),(7,4,'2024-07-02 00:00:00',20000,'completed'),(8,4,'2024-06-25 00:00:00',5000,'cancelled');
Вывод
1|0|2|1500|active|1
2|180|3|10000|vip|0
3|182|1|100|churned|0
4||0|0|no_orders|0
5||0|0|no_orders|0
РешениеSQL · SQLite

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

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

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