Как посчитать повторные покупки в SQL
Карьерник — квиз-тренажёр в Telegram с 1500+ вопросами для собесов аналитика. SQL, Python, A/B, метрики. Бесплатно.
Зачем считать повторные покупки
Новый клиент дороже повторного в 5-25 раз — это мантра retention-маркетинга. Поэтому repeat purchase rate (процент клиентов, сделавших >1 заказа) — одна из главных метрик e-commerce. Продукты с repeat rate 40% живут на организике, с 10% — сжигают деньги на paid acquisition.
На собесе в e-commerce компании (Wildberries, Ozon, Магнит, Delivery Club) это must-know задача: «посчитай долю повторных покупателей», «найди среднее время между заказами», «построй когортный repeat rate». Без понимания ROW_NUMBER, LAG, MIN/MAX за стол не сесть.
Плюс есть нюансы: repeat rate считать от всех клиентов или только от «успешных»? Покупка 2 раза за неделю — это repeat или double-ordering? Ушедших клиентов включать?
В статье — готовые запросы:
- Базовый repeat purchase rate
- Доля клиентов с 2+, 3+, 5+ заказами
- Среднее время между покупками
- Repeat rate по когортам (стало ли лучше?)
- Frequency (покупок на клиента в месяц)
- Revenue от повторных vs новых
Схема: orders(order_id, user_id, total, status, created_at).
1. Базовый repeat purchase rate
WITH buyer_orders AS (
SELECT
user_id,
COUNT(*) AS orders_count
FROM orders
WHERE status = 'paid'
GROUP BY user_id
)
SELECT
COUNT(*) AS total_buyers,
COUNT(*) FILTER (WHERE orders_count >= 2) AS repeat_buyers,
100.0 * COUNT(*) FILTER (WHERE orders_count >= 2) / COUNT(*) AS repeat_rate_pct
FROM buyer_orders;2. Распределение по количеству заказов
WITH buyer_orders AS (
SELECT user_id, COUNT(*) AS orders_count
FROM orders WHERE status = 'paid'
GROUP BY user_id
)
SELECT
orders_count,
COUNT(*) AS buyers,
100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_buyers
FROM buyer_orders
GROUP BY orders_count
ORDER BY orders_count;Обычно: 60% делают 1 покупку, 20% — 2, 10% — 3, 5% — 4, 5% — 5+.
3. Доля клиентов с 2+, 3+, 5+
WITH buyer_orders AS (
SELECT user_id, COUNT(*) AS cnt
FROM orders WHERE status = 'paid'
GROUP BY user_id
),
total AS (SELECT COUNT(*) AS n FROM buyer_orders)
SELECT
100.0 * COUNT(*) FILTER (WHERE cnt >= 1) / n AS pct_1_plus,
100.0 * COUNT(*) FILTER (WHERE cnt >= 2) / n AS pct_2_plus,
100.0 * COUNT(*) FILTER (WHERE cnt >= 3) / n AS pct_3_plus,
100.0 * COUNT(*) FILTER (WHERE cnt >= 5) / n AS pct_5_plus,
100.0 * COUNT(*) FILTER (WHERE cnt >= 10) / n AS pct_10_plus
FROM buyer_orders, total;4. Время между покупками
WITH ordered AS (
SELECT
user_id,
created_at,
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order
FROM orders
WHERE status = 'paid'
)
SELECT
user_id,
AVG(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS avg_days_between,
MIN(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS min_days,
MAX(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS max_days
FROM ordered
WHERE prev_order IS NOT NULL
GROUP BY user_id;5. Среднее время до второй покупки
Отдельный KPI — «time to second purchase». Ключевой индикатор retention.
WITH user_orders AS (
SELECT
user_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders
WHERE status = 'paid'
)
SELECT
AVG(EXTRACT(EPOCH FROM (o2.created_at - o1.created_at)) / 86400) AS avg_days_to_2nd,
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (o2.created_at - o1.created_at)) / 86400
) AS median_days_to_2nd
FROM user_orders o1
JOIN user_orders o2 ON o2.user_id = o1.user_id AND o2.order_num = 2
WHERE o1.order_num = 1;6. Repeat rate по когортам
Смотрим, улучшается ли retention новых когорт.
WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort
FROM orders
WHERE status = 'paid'
GROUP BY user_id
),
user_stats AS (
SELECT user_id, COUNT(*) AS orders_count
FROM orders WHERE status = 'paid'
GROUP BY user_id
)
SELECT
c.cohort,
COUNT(*) AS cohort_size,
COUNT(*) FILTER (WHERE us.orders_count >= 2) AS repeat_buyers,
100.0 * COUNT(*) FILTER (WHERE us.orders_count >= 2) / COUNT(*) AS repeat_rate
FROM cohorts c
JOIN user_stats us USING (user_id)
GROUP BY c.cohort
ORDER BY c.cohort;7. Frequency — заказов на клиента в месяц
WITH user_activity AS (
SELECT
user_id,
COUNT(*) AS total_orders,
EXTRACT(MONTH FROM AGE(MAX(created_at), MIN(created_at))) + 1 AS active_months
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING COUNT(*) >= 2 -- только multi-buyers
)
SELECT
AVG(total_orders::FLOAT / NULLIF(active_months, 0)) AS avg_frequency_per_month,
PERCENTILE_CONT(0.5) WITHIN GROUP (
ORDER BY total_orders::FLOAT / NULLIF(active_months, 0)
) AS median_frequency
FROM user_activity;8. Revenue от повторных vs новых
WITH user_order_num AS (
SELECT
user_id,
total,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
FROM orders
WHERE status = 'paid'
)
SELECT
CASE WHEN order_num = 1 THEN 'first_purchase' ELSE 'repeat' END AS type,
COUNT(*) AS orders,
SUM(total) AS revenue,
AVG(total) AS avg_order_value
FROM user_order_num
GROUP BY 1;Обычно repeat покупатели имеют выше AOV.
Частые ошибки
Включать trial / test accounts
Очищайте от сотрудников и ботов.
Not distinguishing double-order
Два заказа за 5 минут = один (split payments). Группируйте.
Когорта «все клиенты»
Новые пользователи ещё не успели сделать repeat. Когорты — обязательно.
Repeat rate без окна
Repeat rate за всё время ≠ за 90 дней. Уточняйте.
Связанные темы
FAQ
Что считается «повторным»?
2+ заказа. Иногда 3+ для более строгой метрики.
Repeat rate 30% — хорошо?
В e-commerce — норма. В food delivery — должно быть 60%+.
Как связано с LTV?
LTV ≈ AOV × frequency × lifespan. Repeat rate прямо влияет на lifespan.
Time to 2nd purchase — что это даёт?
Прокси для product-market fit. Короткое время → клиенты ценят.
Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.