Условие
Таблица orders(user_id, order_id, order_date, amount). Для каждой когорты по месяцу первой покупки найдите:
- сколько уникальных юзеров сделали первую покупку,
- из них сколько вернулись и сделали второй заказ в течение 30 дней,
repeat_30d_rateв процентах.
Решение
WITH first_orders AS (
SELECT
user_id,
MIN(order_date) AS first_date
FROM orders
GROUP BY user_id
),
cohorted AS (
SELECT
DATE_TRUNC('month', first_date)::DATE AS cohort_month,
user_id,
first_date
FROM first_orders
),
second_order AS (
SELECT
o.user_id,
MIN(o.order_date) AS second_date
FROM orders o
JOIN first_orders f ON f.user_id = o.user_id
WHERE o.order_date > f.first_date
GROUP BY o.user_id
)
SELECT
c.cohort_month,
COUNT(c.user_id) AS new_users,
COUNT(s.user_id) FILTER (
WHERE s.second_date <= c.first_date + INTERVAL '30 days'
) AS repeated_30d,
ROUND(
100.0 * COUNT(s.user_id) FILTER (
WHERE s.second_date <= c.first_date + INTERVAL '30 days'
) / COUNT(c.user_id),
2
) AS repeat_30d_rate
FROM cohorted c
LEFT JOIN second_order s ON s.user_id = c.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;Идея
first_orders— первый заказ каждого юзера.cohorted— нарезка по месяцу первой покупки.second_order— первый заказ после первого.- Сравниваем
second_date <= first_date + 30 days.
Альтернатива через LEAD()
WITH ranked AS (
SELECT
user_id, order_date,
LEAD(order_date) OVER (PARTITION BY user_id ORDER BY order_date) AS next_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS rn
FROM orders
)
SELECT
DATE_TRUNC('month', order_date)::DATE AS cohort_month,
COUNT(*) AS new_users,
SUM(CASE WHEN next_date <= order_date + INTERVAL '30 days' THEN 1 ELSE 0 END) AS repeated_30d
FROM ranked
WHERE rn = 1
GROUP BY 1
ORDER BY 1;Короче, но скрывает шаг «второй заказ» — менее читабельно для джунов.
Подводные камни
- Незрелая когорта. Юзеры с
first_date > CURRENT_DATE - 30 daysещё не имели шанса повторить — нужно либо исключать, либо помечатьrepeat_30d_rate = NULL. - «В течение 30 дней» =
<=или<? Обычно<= first_date + 30. order_date > first_dateстрогое. Если строго не больше — вsecond_orderпопадёт сам first.
Эталонный ответ
MIN(order_date) per user → cohort_month → MIN(order_date WHERE > first_date) → проверка ≤ first + 30d. Незрелые когорты явно отрезать.