SQL Studio / Разборы SQL / Через сколько дней покупают снова — и кто исчез из среднего
Отчёт утверждает, что повторную покупку ждут в среднем 10,5 дня. Но это среднее рассчитано только по четырём вернувшимся клиентам; ещё двое оформили первый заказ, который впоследствии был доставлен, но не оформили второго такого заказа до среза. Поэтому число нельзя назвать средним ожиданием всей когорты. На SQLite сначала нумеруем доставленные заказы через ROW_NUMBER(), а затем храним отдельно интервал до второй покупки и длительность наблюдения для тех, у кого её не было.
Берём покупателей, чей первый заказ со статусом delivered был оформлен с 1 января до 1 февраля 2026 года. Поле placed_at означает момент оформления, а не фактическую дату доставки: здесь мы измеряем интервал между оформлениями заказов, которые впоследствии доставлены. Чтобы измерять время между доставками, нужно отдельное поле delivered_at. Наблюдение заканчивается 1 марта 2026 года, не включая этот день. Даты в UTC. Метрика здесь — разница календарных дат, а не точные 24-часовые отрезки; два заказа одного дня дают 0 дней.
Неполный ответ: среднее только по вернувшимся
Фильтр second_at IS NOT NULL не ошибочен сам по себе. Ошибка — забыть, что он изменил аудиторию, и назвать результат средним всей январской когорты.
WITH orders(order_id, customer_id, placed_at, status) AS (
VALUES (101,1,'2026-01-01 10:00:00','delivered'), (102,1,'2026-01-03 09:00:00','delivered'),
(201,2,'2026-01-05 08:00:00','delivered'), (202,2,'2026-01-15 11:00:00','delivered'),
(301,3,'2026-01-10 10:00:00','delivered'), (302,3,'2026-02-09 10:00:00','delivered'),
(401,4,'2026-01-15 15:00:00','delivered'), (402,4,'2026-01-16 10:00:00','cancelled'),
(501,5,'2026-01-20 09:00:00','delivered'), (502,5,'2026-03-03 09:00:00','delivered'),
(601,6,'2026-01-26 10:00:00','delivered'), (602,6,'2026-01-26 14:00:00','delivered'),
(701,7,'2025-12-31 23:00:00','delivered'), (702,7,'2026-01-02 09:00:00','delivered'),
(801,8,'2026-02-01 09:00:00','delivered'), (802,8,'2026-02-05 09:00:00','delivered')
),
numbered AS (
SELECT order_id, customer_id, placed_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id) AS rn
FROM orders
WHERE status='delivered' AND placed_at < '2026-03-01'
),
first_second AS (
SELECT customer_id,
MAX(CASE WHEN rn=1 THEN placed_at END) AS first_at,
MAX(CASE WHEN rn=2 THEN placed_at END) AS second_at
FROM numbered GROUP BY customer_id
),
jan_cohort AS (
SELECT customer_id, first_at, second_at,
CAST(julianday(date(second_at))-julianday(date(first_at)) AS INTEGER) AS days_to_second,
CAST(julianday('2026-03-01')-julianday(date(first_at)) AS INTEGER) AS observed_days
FROM first_second
WHERE first_at >= '2026-01-01' AND first_at < '2026-02-01'
)
SELECT ROUND(AVG(days_to_second),1) AS avg_returners_days,
COUNT(*) AS returned_customers
FROM jan_cohort
WHERE second_at IS NOT NULL;| Среднее, дней | Вернулись |
|---|---|
| 10.5 | 4 |
Четыре интервала — 0, 2, 10 и 30 дней. Их сумма — 42, среднее — 10,5. Отменённый заказ клиента №4 не является второй учитываемой покупкой; второй доставленный заказ клиента №5 был оформлен 3 марта, после среза, и не включается.
Первая, вторая покупка и незавершённое наблюдение
Сортировка ORDER BY placed_at, order_id определяет порядок даже при одинаковых timestamp. Сначала нумеруем все оформленные до среза заказы со статусом delivered, затем отбираем январскую когорту по дате оформления первого такого заказа. Иначе декабрьский покупатель №7 ошибочно стал бы «новым январским». Покупатель №8 впервые купил в феврале и тоже не попадает сюда.
WITH orders(order_id, customer_id, placed_at, status) AS (
VALUES (101,1,'2026-01-01 10:00:00','delivered'), (102,1,'2026-01-03 09:00:00','delivered'),
(201,2,'2026-01-05 08:00:00','delivered'), (202,2,'2026-01-15 11:00:00','delivered'),
(301,3,'2026-01-10 10:00:00','delivered'), (302,3,'2026-02-09 10:00:00','delivered'),
(401,4,'2026-01-15 15:00:00','delivered'), (402,4,'2026-01-16 10:00:00','cancelled'),
(501,5,'2026-01-20 09:00:00','delivered'), (502,5,'2026-03-03 09:00:00','delivered'),
(601,6,'2026-01-26 10:00:00','delivered'), (602,6,'2026-01-26 14:00:00','delivered'),
(701,7,'2025-12-31 23:00:00','delivered'), (702,7,'2026-01-02 09:00:00','delivered'),
(801,8,'2026-02-01 09:00:00','delivered'), (802,8,'2026-02-05 09:00:00','delivered')
),
numbered AS (
SELECT order_id, customer_id, placed_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id) AS rn
FROM orders
WHERE status='delivered' AND placed_at < '2026-03-01'
),
first_second AS (
SELECT customer_id,
MAX(CASE WHEN rn=1 THEN placed_at END) AS first_at,
MAX(CASE WHEN rn=2 THEN placed_at END) AS second_at
FROM numbered GROUP BY customer_id
),
jan_cohort AS (
SELECT customer_id, first_at, second_at,
CAST(julianday(date(second_at))-julianday(date(first_at)) AS INTEGER) AS days_to_second,
CAST(julianday('2026-03-01')-julianday(date(first_at)) AS INTEGER) AS observed_days
FROM first_second
WHERE first_at >= '2026-01-01' AND first_at < '2026-02-01'
)
SELECT customer_id, date(first_at) AS first_day,
date(second_at) AS second_day,
days_to_second, observed_days,
CASE WHEN second_at IS NULL THEN 0 ELSE 1 END AS has_returned
FROM jan_cohort
ORDER BY customer_id;| Клиент | Первая | Вторая | До второй, дней | Наблюдали, дней | Вернулся |
|---|---|---|---|---|---|
| 1 | 2026-01-01 | 2026-01-03 | 2 | 59 | 1 |
| 2 | 2026-01-05 | 2026-01-15 | 10 | 55 | 1 |
| 3 | 2026-01-10 | 2026-02-09 | 30 | 50 | 1 |
| 4 | 2026-01-15 | NULL | NULL | 45 | 0 |
| 5 | 2026-01-20 | NULL | NULL | 40 | 0 |
| 6 | 2026-01-26 | 2026-01-26 | 0 | 34 | 1 |
Клиенты №4 и №5 имеют NULL для второй даты и интервала, а не 0. Для №4 известно лишь, что за 45 календарных дней после оформления первого учитываемого заказа второго не было; для №5 — за 40 дней. Это правое цензурирование: событие могло случиться после закрытия данных. Если подставить сюда 0 или отфильтровать обоих без пояснений, смысл метрики пропадёт.
Медиана по вернувшимся — не медиана для всей когорты
У четырёх наблюдаемых интервалов отсортированный ряд 0, 2, 10, 30. Для чётного количества медиана — среднее двух центральных элементов, то есть 6 дней. В SQLite нет встроенной агрегатной MEDIAN() в используемой учебной конфигурации, поэтому ранжируем значения оконными функциями. Два цензурированных клиента остаются в знаменателе общей численности, но не превращаются в фиктивные времена покупки.
WITH orders(order_id, customer_id, placed_at, status) AS (
VALUES (101,1,'2026-01-01 10:00:00','delivered'), (102,1,'2026-01-03 09:00:00','delivered'),
(201,2,'2026-01-05 08:00:00','delivered'), (202,2,'2026-01-15 11:00:00','delivered'),
(301,3,'2026-01-10 10:00:00','delivered'), (302,3,'2026-02-09 10:00:00','delivered'),
(401,4,'2026-01-15 15:00:00','delivered'), (402,4,'2026-01-16 10:00:00','cancelled'),
(501,5,'2026-01-20 09:00:00','delivered'), (502,5,'2026-03-03 09:00:00','delivered'),
(601,6,'2026-01-26 10:00:00','delivered'), (602,6,'2026-01-26 14:00:00','delivered'),
(701,7,'2025-12-31 23:00:00','delivered'), (702,7,'2026-01-02 09:00:00','delivered'),
(801,8,'2026-02-01 09:00:00','delivered'), (802,8,'2026-02-05 09:00:00','delivered')
),
numbered AS (
SELECT order_id, customer_id, placed_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id) AS rn
FROM orders
WHERE status='delivered' AND placed_at < '2026-03-01'
),
first_second AS (
SELECT customer_id,
MAX(CASE WHEN rn=1 THEN placed_at END) AS first_at,
MAX(CASE WHEN rn=2 THEN placed_at END) AS second_at
FROM numbered GROUP BY customer_id
),
jan_cohort AS (
SELECT customer_id, first_at, second_at,
CAST(julianday(date(second_at))-julianday(date(first_at)) AS INTEGER) AS days_to_second,
CAST(julianday('2026-03-01')-julianday(date(first_at)) AS INTEGER) AS observed_days
FROM first_second
WHERE first_at >= '2026-01-01' AND first_at < '2026-02-01'
),
ranked_returns AS (
SELECT days_to_second,
ROW_NUMBER() OVER (ORDER BY days_to_second, customer_id) AS pos,
COUNT(*) OVER () AS n
FROM jan_cohort WHERE second_at IS NOT NULL
)
SELECT (SELECT COUNT(*) FROM jan_cohort) AS cohort_n,
(SELECT COUNT(*) FROM jan_cohort WHERE second_at IS NOT NULL) AS returned_n,
(SELECT COUNT(*) FROM jan_cohort WHERE second_at IS NULL) AS censored_n,
(SELECT AVG(days_to_second) FROM ranked_returns
WHERE pos IN ((n+1)/2,(n+2)/2)) AS median_returners_days
;| Когорта | С 2-й покупкой | Цензурированы | Медиана вернувшихся |
|---|---|---|---|
| 6 | 4 | 2 | 6.0 |
Итак, 6 из 6 имели первую покупку в январе; только 4 из 6 вернулись до среза, а 2 из 6 остаются под наблюдением без второй покупки. Медиана 6 дней описывает исключительно этих четверых, а не «обычного нового покупателя» вообще.
Когда половина всей когорты успела вернуться?
В этом конкретном наборе цензурирование случается на 40-м и 45-м днях, уже после всех четырёх зафиксированных возвратов (до 30-го дня). Поэтому до дня 30 простая накопленная доля событий от всех шести совпадает с оценкой возврата для наблюдаемой когорты: на 0-й день вернулся 1, на 2-й — 2, на 10-й — 3, на 30-й — 4.
WITH orders(order_id, customer_id, placed_at, status) AS (
VALUES (101,1,'2026-01-01 10:00:00','delivered'), (102,1,'2026-01-03 09:00:00','delivered'),
(201,2,'2026-01-05 08:00:00','delivered'), (202,2,'2026-01-15 11:00:00','delivered'),
(301,3,'2026-01-10 10:00:00','delivered'), (302,3,'2026-02-09 10:00:00','delivered'),
(401,4,'2026-01-15 15:00:00','delivered'), (402,4,'2026-01-16 10:00:00','cancelled'),
(501,5,'2026-01-20 09:00:00','delivered'), (502,5,'2026-03-03 09:00:00','delivered'),
(601,6,'2026-01-26 10:00:00','delivered'), (602,6,'2026-01-26 14:00:00','delivered'),
(701,7,'2025-12-31 23:00:00','delivered'), (702,7,'2026-01-02 09:00:00','delivered'),
(801,8,'2026-02-01 09:00:00','delivered'), (802,8,'2026-02-05 09:00:00','delivered')
),
numbered AS (
SELECT order_id, customer_id, placed_at,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at, order_id) AS rn
FROM orders
WHERE status='delivered' AND placed_at < '2026-03-01'
),
first_second AS (
SELECT customer_id,
MAX(CASE WHEN rn=1 THEN placed_at END) AS first_at,
MAX(CASE WHEN rn=2 THEN placed_at END) AS second_at
FROM numbered GROUP BY customer_id
),
jan_cohort AS (
SELECT customer_id, first_at, second_at,
CAST(julianday(date(second_at))-julianday(date(first_at)) AS INTEGER) AS days_to_second,
CAST(julianday('2026-03-01')-julianday(date(first_at)) AS INTEGER) AS observed_days
FROM first_second
WHERE first_at >= '2026-01-01' AND first_at < '2026-02-01'
),
event_days AS (
SELECT days_to_second AS day, COUNT(*) AS returned_on_day
FROM jan_cohort WHERE second_at IS NOT NULL
GROUP BY days_to_second
)
SELECT day, returned_on_day,
SUM(returned_on_day) OVER (ORDER BY day) AS returned_by_day,
(SELECT COUNT(*) FROM jan_cohort) AS cohort_n
FROM event_days
ORDER BY day;| День от первой | В этот день | Вернулись всего | Когорта |
|---|---|---|---|
| 0 | 1 | 1 | 6 |
| 2 | 1 | 2 | 6 |
| 10 | 1 | 3 | 6 |
| 30 | 1 | 4 | 6 |
Половина всей когорты (3 из 6) наблюдалась со второй покупкой уже к 10-му календарному дню. Это отдельный вопрос и не равно медиане 6 по вернувшимся. В другом наборе, где люди теряются из наблюдения до нужного срока, обычный накопленный подсчёт уже нельзя интерпретировать как вероятность возврата: нужны таблица риска и методы анализа выживаемости, а медиана по всей когорте может быть не определена.
Небольшая проверка
Сдвиньте дату среза с 1 на 5 марта: какой второй заказ, оформленный после прежнего среза, попадёт в результат и как изменится число цензурированных? Не забудьте сохранить январский фильтр на дату оформления первого доставленного заказа. Сначала попробуйте запрос в свободном SQLite-редакторе; затем сравните с долей повторных покупателей и когортным retention. За устойчивой нумерацией строк обратитесь к оконной сортировке с tie-break.
Для задач на MySQL 8 вместо SQLite — отдельный курс на Stepik. Синтаксис дат и оконных функций у движков может отличаться; запросы этой статьи проверены именно в SQLite.