Выручка с покупателя за 12 месяцев
SQL, по которому посчитано
WITH won AS (
SELECT customer_id, date, amount FROM deals WHERE status = 'Выиграна'
),
first_purchase AS (
SELECT customer_id, min(date) AS first_date FROM won GROUP BY customer_id
)
SELECT date_trunc('month', first_purchase.first_date) AS cohort,
round(sum(won.amount) / count(DISTINCT first_purchase.customer_id)) AS ltv
FROM first_purchase
JOIN won USING (customer_id)
WHERE won.date < first_purchase.first_date + INTERVAL 12 MONTH
AND first_purchase.first_date <= (SELECT max(date) FROM deals) - INTERVAL 12 MONTH
GROUP BY cohort
ORDER BY cohort