Средний интервал между покупками по категориям
SQL, по которому посчитано
WITH won AS (
SELECT customer_id, date, category
FROM deals
WHERE status = 'Выиграна'
AND customer_id IS NOT NULL
),
with_prev AS (
SELECT customer_id, date, category,
LAG(date) OVER (PARTITION BY customer_id ORDER BY date) AS prev_date
FROM won
),
intervals AS (
SELECT category,
date_diff('day', prev_date, date) AS interval_days
FROM with_prev
WHERE prev_date IS NOT NULL
AND date_diff('day', prev_date, date) > 0
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
)
SELECT category,
count(*) AS repeat_purchases,
round(avg(interval_days), 1) AS avg_interval_days,
round(median(interval_days), 1) AS median_interval_days
FROM intervals
GROUP BY category
ORDER BY avg_interval_days DESC