LTV когорт за 12 месяцев
SQL, по которому посчитано
WITH won AS (SELECT customer_id, date, amount FROM deals WHERE status = 'Выиграна' AND amount > 0), first_deal AS (SELECT customer_id, min(date) AS first_date FROM won GROUP BY customer_id), cohorts AS (SELECT customer_id, first_date, date_trunc('month', first_date) AS cohort_month FROM first_deal WHERE first_date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH), customer_revenue AS (SELECT c.cohort_month, c.customer_id, sum(w.amount) AS revenue_12m FROM cohorts c JOIN won w ON w.customer_id = c.customer_id AND w.date >= c.first_date AND w.date < c.first_date + INTERVAL 12 MONTH GROUP BY c.cohort_month, c.customer_id) SELECT cohort_month, count(*) AS customers, round(sum(revenue_12m) / count(*), 0) AS ltv_12m FROM customer_revenue GROUP BY cohort_month ORDER BY cohort_month