Как посчитать Days Between Orders в SQL

Зачем Days Between Orders

В e-com знание среднего интервала между заказами помогает: 1) timing для retention email, 2) идентификация churn (если юзер давно не покупал относительно среднего), 3) сравнение sub-categories.

Формула

Days Between Orders = AVG(current_order_date - prev_order_date)
                       для пар последовательных заказов одного user

Базовый расчёт

WITH order_gaps 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
    AVG(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS avg_days_between,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400
    ) AS median_days_between
FROM order_gaps
WHERE prev_order IS NOT NULL;

Distribution

WITH gaps AS (
    SELECT
        EXTRACT(EPOCH FROM (created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at))) / 86400 AS days
    FROM orders
    WHERE status = 'paid'
)
SELECT
    CASE
        WHEN days <= 7 THEN '0-7 days'
        WHEN days <= 30 THEN '8-30 days'
        WHEN days <= 90 THEN '31-90 days'
        WHEN days <= 180 THEN '91-180 days'
        ELSE '180+ days'
    END AS bucket,
    COUNT(*) AS count
FROM gaps
WHERE days IS NOT NULL
GROUP BY 1
ORDER BY 1;

По сегментам

По category:

WITH gaps AS (
    SELECT
        user_id,
        category,
        created_at,
        LAG(created_at) OVER (PARTITION BY user_id, category ORDER BY created_at) AS prev_order
    FROM orders
    WHERE status = 'paid'
)
SELECT
    category,
    AVG(EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400) AS avg_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (created_at - prev_order)) / 86400
    ) AS median_days
FROM gaps
WHERE prev_order IS NOT NULL
GROUP BY category
ORDER BY median_days;

FMCG — 7-30 days. Одежда — 30-90. Электроника — 180-365.

Частые ошибки

Ошибка 1. Считать на single-purchase users. LAG = NULL. Отфильтруйте.

Ошибка 2. Avg vs median. Avg чувствителен к outliers (юзер вернулся через 2 года). Median — устойчиво.

Ошибка 3. Same-day orders. Юзер сделал 2 заказа в один день — days = 0. Это технически verна, но в категории «между заказами» это шум. Cap minimum >= 1.

Ошибка 4. Cross-category aggregation. FMCG и Электроника в одной таблице — avg говорит мало. Сегментируйте.

Ошибка 5. Survivorship bias. Если считаете на «активных юзерах», average days короче. Включите всех.

Связанные темы

• Как посчитать frequency в SQL
• Как посчитать repeat purchase rate в SQL
• Как посчитать AOV в SQL
• Как посчитать retention в SQL

FAQ

Какой Days Between Orders считается ok?

Зависит от категории. FMCG: 7-21. Одежда: 30-90. Электроника: 180-540.

Как использовать для retention?

Если юзер last purchase > 1.5 × average days → likely churning. Trigger email.

Distribution vs average?

Distribution показывает реальный shape. Average может скрывать bimodal pattern.

Same-day cap?

Cap >= 1 days. Иначе same-cart split на 2 orders искажает.

По категории или по user?

Обе. По user — для loyalty / churn. По категории — для marketing strategy.