LatornLatorn
Войти

SQL: RFM-анализ на дату среза — Статистика клиентских RFM-сегментов

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

Статистика клиентских RFM-сегментов

Что уже дано

Таблицы clients(client_id INTEGER PRIMARY KEY, registration_date TEXT NOT NULL) и purchases(purchase_id INTEGER PRIMARY KEY, client_id INTEGER NOT NULL, purchase_time TEXT NOT NULL, total_rub INTEGER NOT NULL, state TEXT NOT NULL). До 500 строк в каждой. ID положительные, покупки принадлежат существующим клиентам. Суммы — целые неотрицательные рубли; state — success или cancelled. Даты UTC YYYY-MM-DD HH:MM:SS в 2023–2024; все клиенты зарегистрированы до среза.

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

На срез 2024-09-01 00:00:00 учитывайте только success-покупки до среза включительно. Для каждого клиента frequency — число таких покупок, monetary — их сумма, recency_days — полностью прошедшие 24-часовые сутки от последней покупки до среза. Без покупок frequency и monetary равны 0, давность NULL. Сегменты: never_bought без покупок; recent_big при давности <= 45 и сумме >= 5000; recent_small при давности <= 45 и сумме < 5000; sleeping при давности > 45. Верните одну строку на каждый представленный сегмент: client_count — число клиентов, total_revenue — сумма их monetary, avg_frequency — среднее frequency по клиентам сегмента, округлённое до 2 знаков. Каждый клиент получает одинаковый вес в среднем.

Ввод и вывод

Отправьте один SELECT или WITH-запрос; stdin и печать не используются. Ровно 4 столбцов в указанном порядке и с этими именами: segment, client_count, total_revenue, avg_frequency. segment — TEXT; client_count и total_revenue — INTEGER; avg_frequency — REAL. NULL нет. Сортировка: total_revenue DESC, client_count DESC, segment ASC. Пустой clients даёт пустой результат; отсутствующие сегменты не выводятся. В тестовых результатах разделитель |, NULL — пустое поле. Допускается любой эквивалентный запрос.

Пример

Подготовка данных
INSERT INTO clients 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 purchases VALUES (1,1,'2024-08-01 00:00:00',6000,'success'),(2,2,'2024-08-01 00:00:00',100,'success'),(3,3,'2024-01-01 00:00:00',8000,'success'),(4,4,'2024-09-02 00:00:00',9000,'success'),(5,5,'2024-08-01 00:00:00',9000,'cancelled');
Вывод
sleeping|1|8000|1.0
recent_big|1|6000|1.0
recent_small|1|100|1.0
never_bought|2|0|0.0
РешениеSQL · SQLite

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

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

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