Отток покупателей по месяцам
SQL, по которому посчитано
WITH months AS (
SELECT DISTINCT date_trunc('month', date) AS month
FROM deals
WHERE status = 'Выиграна'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
),
buyers AS (
SELECT DISTINCT customer_id, date_trunc('month', date) AS month
FROM deals
WHERE status = 'Выиграна'
AND date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
),
prev AS (
SELECT m.month, COUNT(DISTINCT b.customer_id) AS prev_buyers
FROM months m
JOIN buyers b ON b.month = CAST(m.month - INTERVAL 1 MONTH AS DATE)
GROUP BY m.month
),
retained AS (
SELECT m.month, COUNT(DISTINCT b.customer_id) AS retained
FROM months m
JOIN buyers b ON b.month = CAST(m.month - INTERVAL 1 MONTH AS DATE)
JOIN buyers c ON c.customer_id = b.customer_id AND c.month = m.month
GROUP BY m.month
)
SELECT p.month,
round((p.prev_buyers - COALESCE(r.retained, 0))::DOUBLE / NULLIF(p.prev_buyers, 0), 3) AS churn_rate
FROM prev p
LEFT JOIN retained r ON r.month = p.month
ORDER BY p.month