SQL Studio / Разборы SQL / Кто ушёл из магазина, если «отменить подписку» нельзя
Руководитель видит четырёх покупателей без заказов в сентябре и объявляет: «Ушли четверо». По заранее выбранному правилу 60 полных дней без доставленной покупки к срезу 1 октября 2026 года 00:00 UTC условно ушли только двое. Для магазина без подписки нет наблюдаемого события «отписался»; SQL может обнаружить длительную паузу, но не доказать окончательный уход. Дату среза и правило нужно показывать рядом с числом.
Вход — синтетические доставленные заказы, отмены и один заказ после среза. Берём заказы с датой строго < '2026-10-01'. Кандидат в покупатели появляется только после первой доставки. Важный вопрос: клиент, впервые купивший 15 сентября, ещё не имел 60 дней наблюдения.
Соблазнительная ошибка: «не покупал в сентябре»
Этот запрос честно находит клиентов, чья последняя доставка пришлась до 1 сентября. Однако ответ «ушёл» он подменяет ответом «не заказывал за один календарный месяц».
WITH purchases(customer_id, order_day, status) AS (
VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
(2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
(3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
(3,'2026-09-25','cancelled'),
(4,'2026-09-15','delivered'),
(5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
(6,'2026-08-15','delivered'),
(7,'2026-09-10','cancelled'),
(1,'2026-10-02','delivered')
),
buyers AS (
SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
FROM purchases
WHERE status = 'delivered' AND order_day < '2026-10-01'
GROUP BY customer_id
)
SELECT customer_id
FROM buyers
WHERE last_day < '2026-09-01'
ORDER BY customer_id;| Клиент |
|---|
| 1 |
| 2 |
| 3 |
| 6 |
Здесь четыре ID: 1, 2, 3 и 6. У клиента №3 прошло 59 дней — он ещё не достиг нашего порога; №6 впервые купил 15 августа, так что окно потенциального 60-дневного молчания для него пока не закрылось. Это не клиенты с одинаковым статусом.
Считаем паузу от последней доставки
Сначала каждому покупателю определяем первую и последнюю доставки. Для порога сравниваем julianday() у двух дат без часового пояса, а не DATE_TRUNC из PostgreSQL. Правило примера: если с первой покупки прошло меньше 60 дней, метку «отток» ещё не присваиваем; иначе 60 дней от последней доставки достаточно для условного churn.
WITH purchases(customer_id, order_day, status) AS (
VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
(2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
(3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
(3,'2026-09-25','cancelled'),
(4,'2026-09-15','delivered'),
(5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
(6,'2026-08-15','delivered'),
(7,'2026-09-10','cancelled'),
(1,'2026-10-02','delivered')
),
buyers AS (
SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
FROM purchases
WHERE status = 'delivered' AND order_day < '2026-10-01'
GROUP BY customer_id
)
SELECT customer_id, first_day, last_day,
CAST(julianday('2026-10-01')-julianday(last_day) AS INTEGER) AS silent_days,
CASE
WHEN julianday('2026-10-01')-julianday(first_day) < 60 THEN 'мало наблюдений'
WHEN julianday('2026-10-01')-julianday(last_day) >= 60 THEN 'условный отток'
ELSE 'ещё активен'
END AS status_60d
FROM buyers ORDER BY customer_id;| Клиент | Первая | Последняя | Без доставки, дней | Метка |
|---|---|---|---|---|
| 1 | 2026-05-10 | 2026-07-01 | 92 | условный отток |
| 2 | 2026-06-01 | 2026-08-02 | 60 | условный отток |
| 3 | 2026-05-20 | 2026-08-03 | 59 | ещё активен |
| 4 | 2026-09-15 | 2026-09-15 | 16 | мало наблюдений |
| 5 | 2026-05-01 | 2026-09-25 | 6 | ещё активен |
| 6 | 2026-08-15 | 2026-08-15 | 47 | мало наблюдений |
№1 молчит 92 дня, №2 — ровно 60; оба удовлетворяют правилу. №3 молчит 59, №5 — 6, оба ещё активны по этому правилу. №4 и №6 молоды: их текущая пауза не позволяет наблюдать полные 60 дней с момента первой доставки. Клиент №7 с единственной отменой не покупатель; доставка №1 от 2 октября находится за датой среза и не должна «лечить» прежнюю историю.
Знаменатель и правое цензурирование
Если просто разделить два «ушедших» на шесть покупателей, получится 33,3%, но в знаменателе окажутся два человека, которые физически не могли наблюдаться 60 дней. В этом учебном отчёте берём только четверых с 60 и более днями истории покупок. Это доля покупателей старшей наблюдаемой группы с текущей паузой ≥60 дней, а не коэффициент оттока за сентябрь или доказательство потери покупателя.
WITH purchases(customer_id, order_day, status) AS (
VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
(2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
(3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
(3,'2026-09-25','cancelled'),
(4,'2026-09-15','delivered'),
(5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
(6,'2026-08-15','delivered'),
(7,'2026-09-10','cancelled'),
(1,'2026-10-02','delivered')
),
buyers AS (
SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
FROM purchases
WHERE status = 'delivered' AND order_day < '2026-10-01'
GROUP BY customer_id
),
classified AS (
SELECT customer_id,
CASE
WHEN julianday('2026-10-01')-julianday(first_day) < 60 THEN 'censored_new'
WHEN julianday('2026-10-01')-julianday(last_day) >= 60 THEN 'churned'
ELSE 'not_churned_yet'
END AS state
FROM buyers
)
SELECT COUNT(*) AS buyers,
SUM(state = 'censored_new') AS censored_new,
SUM(state = 'churned') AS churned,
SUM(state = 'not_churned_yet') AS not_churned_yet,
ROUND(100.0 * SUM(state = 'churned') /
NULLIF(SUM(state <> 'censored_new'), 0), 1) AS churn_share_eligible_pct
FROM classified;| Всего | Новички: недостаточно окна | Условно ушли | Пока активны | Доля среди 4, % |
|---|---|---|---|---|
| 6 | 2 | 2 | 2 | 50.0 |
Числа: 2 из 4 = 50,0% для выбранной наблюдаемой группы. Назовите этот показатель в отчёте «доля с паузой 60+ дней», не подписывайте его как месячный churn rate или вероятность никогда не вернуться. Клиенты без нового заказа к срезу правосторонне цензурированы относительно будущего возврата: мы не знаем, купят ли они позже. Новички №4 и №6 ещё и не имеют полного 60-дневного окна для заданной метки.
Один день решает границу
У клиента №2 последняя доставка была 2 августа; у №3 — 3 августа. На 1 октября, исключая сам день среза, это ровно 60 и 59 дней. Здесь >= 60 принципиально, а «больше 60» вернуло бы другой результат.
WITH purchases(customer_id, order_day, status) AS (
VALUES (1,'2026-05-10','delivered'), (1,'2026-07-01','delivered'),
(2,'2026-06-01','delivered'), (2,'2026-08-02','delivered'),
(3,'2026-05-20','delivered'), (3,'2026-08-03','delivered'),
(3,'2026-09-25','cancelled'),
(4,'2026-09-15','delivered'),
(5,'2026-05-01','delivered'), (5,'2026-09-25','delivered'),
(6,'2026-08-15','delivered'),
(7,'2026-09-10','cancelled'),
(1,'2026-10-02','delivered')
),
buyers AS (
SELECT customer_id, MIN(order_day) AS first_day, MAX(order_day) AS last_day
FROM purchases
WHERE status = 'delivered' AND order_day < '2026-10-01'
GROUP BY customer_id
)
SELECT customer_id, last_day,
CAST(julianday('2026-10-01')-julianday(last_day) AS INTEGER) AS silent_days,
CASE WHEN last_day <= date('2026-10-01','-60 days') THEN 1 ELSE 0 END AS inactivity_60d
FROM buyers
WHERE customer_id IN (2,3)
ORDER BY customer_id;| Клиент | Последняя | Дней | Пауза ≥60 |
|---|---|---|---|
| 2 | 2026-08-02 | 60 | 1 |
| 3 | 2026-08-03 | 59 | 0 |
Мини-эксперимент
Что произойдёт с меткой №3, если передвинуть дату среза на 2 октября, сохранив всё остальное? А что изменится, если ошибочно засчитать его отмену от 25 сентября как покупку? Сверьте ответы в SQL-песочнице. Для соседнего вопроса о давности и частоте заказов полезен RFM-разбор; для доли покупателей с повтором — повторные покупки; сравнение когорт — в retention M1/M2.
Для задач на MySQL 8 вместо SQLite — отдельный курс на Stepik. Синтаксис дат и оконных функций у движков может отличаться; запросы этой статьи проверены именно в SQLite.